Replace per VBA
 

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

--
T De Villier
-----------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647
View this thread: http://www.excelforum.com/showthread.php?threadid=56656

Replace per VBA
 

basic premise is as follows:

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


You could also use OFFSET as well.


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=566569

Replace per VBA
 

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
------------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566569

Replace per VBA
 

Actually, realised that all I have to do is:

worksheetfunction.replac

--
T De Villier
-----------------------------------------------------------------------
T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647
View this thread: http://www.excelforum.com/showthread.php?threadid=56656

Replace per VBA
 

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