PDA

View Full Version : [SOLVED] Blank cell macro problem..



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

Richie(UK)
07-09-2004, 03:40 AM
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:


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

Richie(UK)
07-09-2004, 03:43 AM
For reference, see also:

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

Zack Barresse
07-09-2004, 08:23 AM
Hi Bartoni!

Check out the VBA Tags (http://www.vbaexpress.com/vbatags.htm) to get your code to post looking like Richie's!