get for loop to go on after blank cell?
 

I have this code which checks through the cells of a column. When i
finds a value it checks through the cases and if it finds a match i
writes data as specified in the case.

The problem I am having is that when it hits a blank cell it stops.
How can I get it to continue on?


Code
-------------------

Columns("J:J").Select
Dim c As Range
For Each c In Selection
Select Case UCase(c)
Case "LTCOL"
Range("I" & c.Row).Value = "1"
Case "MAJ"
Range("I" & c.Row).Value = "2"
Case "CAPT"
Range("I" & c.Row).Value = "3"
Case "LT"
Range("I" & c.Row).Value = "4"
Case "WO1"
Range("I" & c.Row).Value = "5"
Case "WO2"
Range("I" & c.Row).Value = "6"
Case "SGT"
Range("I" & c.Row).Value = "7"
Case "CPL"
Range("I" & c.Row).Value = "8"
Case "LCPL"
Range("I" & c.Row).Value = "9"
Case "PTE"
Range("I" & c.Row).Value = "10"
Case "APS"
Range("I" & c.Row).Value = "11"
Case ""
Range("I" & c.Row).Value = "12"

Exit For
End Select

Next c

-------------------


--
Message posted from http://www.ExcelForum.com

get for loop to go on after blank cell?
 

Try adding

Select Case ""
' do nothing


"john_t_h " wrote in message
...
I have this code which checks through the cells of a column. When it
finds a value it checks through the cases and if it finds a match it
writes data as specified in the case.

The problem I am having is that when it hits a blank cell it stops.
How can I get it to continue on?


Code:
--------------------

Columns("J:J").Select
Dim c As Range
For Each c In Selection
Select Case UCase(c)
Case "LTCOL"
Range("I" & c.Row).Value = "1"
Case "MAJ"
Range("I" & c.Row).Value = "2"
Case "CAPT"
Range("I" & c.Row).Value = "3"
Case "LT"
Range("I" & c.Row).Value = "4"
Case "WO1"
Range("I" & c.Row).Value = "5"
Case "WO2"
Range("I" & c.Row).Value = "6"
Case "SGT"
Range("I" & c.Row).Value = "7"
Case "CPL"
Range("I" & c.Row).Value = "8"
Case "LCPL"
Range("I" & c.Row).Value = "9"
Case "PTE"
Range("I" & c.Row).Value = "10"
Case "APS"
Range("I" & c.Row).Value = "11"
Case ""
Range("I" & c.Row).Value = "12"

Exit For
End Select

Next c

--------------------



---
Message posted from http://www.ExcelForum.com/

get for loop to go on after blank cell?
 

Would any ideas here help? This is not complete, just something thrown
together to give an idea.

v = Array("LTCOL", "MAJ", "CAPT", "LT", "WO1", "WO2", "SGT", "CPL",
"LCPL", "PTE", "APS")

For Each c In Columns("J:J").SpecialCells(xlTextValues)
Cells(c.Row, 9) = WorksheetFunction.Match(c.Value, v)
Next


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"john_t_h " wrote in message
...
I have this code which checks through the cells of a column. When it
finds a value it checks through the cases and if it finds a match it
writes data as specified in the case.

The problem I am having is that when it hits a blank cell it stops.
How can I get it to continue on?


Code:
--------------------

Columns("J:J").Select
Dim c As Range
For Each c In Selection
Select Case UCase(c)
Case "LTCOL"
Range("I" & c.Row).Value = "1"
Case "MAJ"
Range("I" & c.Row).Value = "2"
Case "CAPT"
Range("I" & c.Row).Value = "3"
Case "LT"
Range("I" & c.Row).Value = "4"
Case "WO1"
Range("I" & c.Row).Value = "5"
Case "WO2"
Range("I" & c.Row).Value = "6"
Case "SGT"
Range("I" & c.Row).Value = "7"
Case "CPL"
Range("I" & c.Row).Value = "8"
Case "LCPL"
Range("I" & c.Row).Value = "9"
Case "PTE"
Range("I" & c.Row).Value = "10"
Case "APS"
Range("I" & c.Row).Value = "11"
Case ""
Range("I" & c.Row).Value = "12"

Exit For
End Select

Next c

--------------------



---
Message posted from http://www.ExcelForum.com/

get for loop to go on after blank cell?
 

What I ended up doing was getting the macro to write some dummy data to
the blank cells by referencing another column that never has blank
cells.

That way I just added in another case for the dummy data and all is
good.

:)


---
Message posted from http://www.ExcelForum.com/