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