Cells(2, 9).Value = Replace(Cells(2, 1).Value,"J", "S")

or just

Cells(2, 9).Value = "S" & Right(Cells(2, 1).Value, Len(Cells(2,1).Value) -
1)

if there could be multiple J



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"T De Villiers"
wrote in message
news:T.De.Villiers.2bt3ri_1154343801.7125@excelfor um-nospam.com...

In Cell A2 I have value J123, In Cell I2 I want S123

In VBA attempting to do this via:
Cells(2, 9) = Replace(Cells(2, 1), 1, 1, "S")

However getting Run-time error 13, Type Mismatch

Need to use this formula as doing this for hundreds of rows.

Many Thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566569