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



"John Bundy" wrote:

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"zaki" wrote:

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