Add Formula to macro to match with adjacent column

[tropics123]

Hello, thank you in advance for your help. I'm trying to add a formula to cell B2 (and going all the way down to match the number of rows in column A) to search for the "L0" in column A and replace both digits before "L0" with another "L0". Example, if the two digits before "L0" is "04", then replace the "04" with "L0".

The formula works fine on a regular excel sheet, however, when I add it as a VBA, the "L0" is creating a problem and macro won't run. Please help!

1) Column A (approximately 1000 lines looking like this with various digits before "L0". In this case it's "04" but it could be "73"). All this data could change with new files we receive:

0012391235064899100012311804L01+00005254.00+0000000.000000+00000000.00+000000.00

<tbody>
</tbody>

2) Here's my VBA code:

Sub AddFormula()
'
Dim LastRowColumnA As Long

LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & LastRowColumnA).Formula = "=IFERROR((REPLACE(A2,(SEARCH("L0",A2)-2),2,"L0")),"")"




End Sub

 

[Fluff]

You need to double-up on the quotes inside the formula like
"=IFERROR((REPLACE(A2,(SEARCH(""L0"",A2)-2),2,""L0"")),"""")"

 

[tropics123]

Awesome...it worked! Thank you ! Just curious, why do the quotes need doubling up?

 

[Fluff]

Anything inside quotes is a string, so without the extra quotes "=IFERROR((REPLACE(A2,(SEARCH("L0",A2)-2),2,"L0")),"")" the parts in red would be treated as separate strings & the two L0 would be treated as variables.