bartoni
07-09-2004, 03:14 AM
: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
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
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