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

  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:[vba]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[/vba]Amend the Sheet1 codename as appropriate.

    HTH

  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,897
    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
  •