macro probs

[zaki]

Having problems with another macro, i dont understand how they randomly just
trip up. The below code is working absolutely fine and changes the desired
text to how i want it to, but for "Applabs" it changes everything in the
corresponding cell to Sub contractor (as it should) BUTbut misses the last
cell out! The last one stays the same. why?

Please help. Appreciate all your help.

Sub subcon()
Dim r As Range
Dim srng As Range
Set srng = Range("e1", Range("e" & Rows.Count). _
End(xlUp)).SpecialCells(xlCellTypeConstants, xlTextValues)
For Each r In srng
Select Case r.Value
Case "AMS Subcon", "Apollo", "Applabs", "Capula", "Temporary Staff"
r.Offset(0, 1).Value = "Sub - Contractor"
End Select
Next r
End Sub

 

[Dave Peterson]

Maybe it's the upper/lower case difference.

I'd use:

Select Case lcase(r.Value)
Case "ams subcon", "apollo", "applabs", "capula", "temporary staff"

Every item in that last line has to be all lower case.

 

[John Bundy]

Works perfectly for me. Not that the way you wrote the code it is case
sensitive, so make sure you fix that or that the sheet matches your criteria.
ie Apollo = Apollo on sheet and not apollo.

 

[Mike H]

Macros don't randomely trip up but do have the infuriating habit of doing
what you tell them to do which is often different to what you ant them to do.

In this case I can see no way why it would ignore a cell containing Applabs
provided that:-

1. The cell is in your selected range
2. Applabs is actualy what you think it is and doesn't have rogue spaces at
the start or end.

Try this on the cell being ignored =len(cell_address) and it should return
7. If it's more then you have spaces.

Mike

 

[zak]

Thanks so much, there was a Ucase L in there which i was too blind to notice!