blank cell problem

[bartoni]

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

 

[Norie]

Rather than

Case Is = ""
Range("U" & MY_ROWS).Value = 10

try

Case Else
Range("U" & MY_ROWS).Value = 10

Put this as the last case statement.

 

[Richie(UK)]

Cross-posting

For reference, see also:

http://www.vbaexpress.com/forum/showthread.php?p=3884#post3884

 

[bartoni]

Norie,

Ive tried the

Case Else
Range("U" & MY_ROWS).Value = 10

and it doesnt add 10 beyond my range which is great but for blank cells it codes the number as 1 and not 10.

??

 

[bartoni]

OK, had a fiddle and sorted it out. Many thanks everyone