Blank cell macro problem..

:confused: 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

Code:

---

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

---

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:

Code:

---

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

For reference, see also:

http://www.mrexcel.com/board2/viewtopic.php?t=96119

Hi Bartoni!

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