blank cell problem

bartoni

Active Member
Joined
Jun 10, 2003
Messages
296
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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.

??
 
Upvote 0

Forum statistics

Threads
1,215,059
Messages
6,122,913
Members
449,093
Latest member
dbomb1414

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top