Replace

J

JB

Hi.
In a very large spreadsheet of exam results I need to change all the A* into
S. The reason I have to do this is because countif doesn't work with the *.
It counts the all A* as well as the A. But they need to be counted
separately.
Trouble is, the replace changes everything that starts with A into S.
I guess it's coz excel thinks the * is a wildcard just like in the countif
formula.
So I had to filter a column, find A* and change the top one and pull down
the cross to change all the others and do that to all the columns.
A bit long winded.
Is there a better way?
Thanks
 
D

Don Guillett

try
Sub repl()
lr = Cells(Rows.Count, "b").End(xlUp).Row
For Each c In Range("b2:b" & lr)
If InStr(1, c, "a*") Then c.Value = "s"
Next
End Sub
 
J

JB

FANTASTIC! Now I don't have to replace. Saved me a lot of time.
I'm glad you came back with the second reply because I'm too dumb to make
out what your first was all about.
Do you think you could please explain the formula for me like what is the
*1 for? and what the product bit means in the sum?
Thank you.
 
Top