Replace per VBA

[T De Villiers]

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 Thank

 

[MattShoreson]

basic premise is as follows:

Cells(2, 9).Formula = "=" & Cells(2 + 121, 9 + 10).Address


You could also use OFFSET as well.

 

[T De Villiers]

Hi

Trying to adapt what youre saying, dont think its working:

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

Thanks

 

[T De Villiers]

Actually, realised that all I have to do is:

worksheetfunction.replac

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...