Consulting

Results 1 to 4 of 4

Thread: Blank cell macro problem..

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location

    Blank cell macro problem..

    This is a recurring problem. Basically i have a list of numbers in column T up to T200 (but this does change) and in column U, i am assigning a number based on the range.
    So:
    (see macro below)
    If a cell in column K is between 0-20, the corresponding cell in U is given a value of 1.

    If a cell in column K is between 21-100, the corresponding cell in U is given a value of 2 etc.

    If there's a blank cell in any cell up to T200, using the macro, I have given the corresponding cell in column U a "10". The problem is when my range finishes i.e no more values in column T, all i have until row 2000 in column U are "10"'s. I thought that this code may do it but it doesnt

    How do I ammend thsi code to say:

    "if there is a blank cell within the data range in column T, give the corresponding cell in column U a value of 10, otherwise leave the cell blank".

    Many Thanks

    Sub itapromo() 
    Columns("U:U").Select 
    Selection.ClearContents 
    Range("T1").Select 
    ActiveCell.FormulaR1C1 = "Promo Y1" 
    For MY_ROWS = 2 To Range("T65536").End(xlUp).Row 
        Select Case Range("T" & MY_ROWS).Value 
            Case Is = "" 
                Range("U" & MY_ROWS).Value = 10 
            Case Is < 20 
                Range("U" & MY_ROWS).Value = 1 
            Case Is < 100 
                Range("U" & MY_ROWS).Value = 2 
            Case Is < 250 
                Range("U" & MY_ROWS).Value = 3 
            Case Is < 500 
                Range("U" & MY_ROWS).Value = 4 
            Case Is < 1000 
                Range("U" & MY_ROWS).Value = 5 
            Case Is < 2000 
                Range("U" & MY_ROWS).Value = 6 
            Case Is < 3500 
                Range("U" & MY_ROWS).Value = 7 
           Case Is < 5000 
                Range("U" & MY_ROWS).Value = 8 
           Case Is < 100000 
                Range("U" & MY_ROWS).Value = 9 
    End Select 
    Next MY_ROWS 
    msg 
    End Sub
    Last edited by Aussiebear; 04-29-2023 at 08:25 PM. Reason: Adjusted the code tags

  2. #2
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    Hi b,

    It's always useful to explicitly state which sheet you are working with - it avoids any confusion between the ActiveSheet (which is what Range and Cells default to) and the sheet that you think that you are acting on. (In your example the various Range references are unqualified.

    This worked on the example data that I tried:

    Sub itapromo()
        Dim MY_ROWS As Long, iVal As Integer
    With Sheet1
        .Columns("U:U").ClearContents
        .Range("T1").Value = "Promo Y1"
        For MY_ROWS = 2 To .Range("T65536").End(xlUp).Row
            Select Case Range("T" & MY_ROWS).Value
                Case Is = "": iVal = 10
                Case Is < 20: iVal = 1
                Case Is < 100: iVal = 2
                Case Is < 250: iVal = 3
                Case Is < 500: iVal = 4
                Case Is < 1000: iVal = 5
                Case Is < 2000: iVal = 6
                Case Is < 3500: iVal = 7
                Case Is < 5000: iVal = 8
                Case Is < 100000: iVal = 9
            End Select
            .Range("U" & MY_ROWS).Value = iVal
        Next MY_ROWS
    End With
    End Sub
    Amend the Sheet1 codename as appropriate.

    HTH
    Last edited by Aussiebear; 04-29-2023 at 08:27 PM. Reason: Adjusted the code tags

  3. #3
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location

    Cross-posting


  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi Bartoni!

    Check out the VBA Tags to get your code to post looking like Richie's!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •