BUTbut misses the last
cell out! The last one stays the same. why?

I don't really know what this means, since your are checking against a
vertical array of cells and posting to one cell to the right. However, if
you mean there is another cell lower in column E that contains one of the
Case criteria and is not posting then it could be one of two things. Your
SpecialCells(xlCellTypeConstants, xlTextValues)
row criteria doesn't include that cell, or the spelling is incorrect. Check
your spelling and the srng value to see if it covers the item being missed.

"Zak" wrote:

(Dont know if this message got sent before, reported an error when sent.)
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

sorted it thanks, it was an uppercase/lowercase issue!

"JLGWhiz" wrote:

BUTbut misses the last
cell out! The last one stays the same. why?

I don't really know what this means, since your are checking against a
vertical array of cells and posting to one cell to the right. However, if
you mean there is another cell lower in column E that contains one of the
Case criteria and is not posting then it could be one of two things. Your
SpecialCells(xlCellTypeConstants, xlTextValues)
row criteria doesn't include that cell, or the spelling is incorrect. Check
your spelling and the srng value to see if it covers the item being missed.

"Zak" wrote:

(Dont know if this message got sent before, reported an error when sent.)
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