get for loop to go on after blank cell?

  • Thread starter Thread starter john_t_h
  • Start date Start date
J

john_t_h

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
 
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
 
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.

:)
 
Back
Top