Error during pasting Excel formula consisting of Mid, Len, Search into VBA module

[reubanrao93]

Hi guys, I'm trying to insert a formula in my code which works in the sheet but not in vba. Running the code below returns a syntax error. I noticed that the errors are due to SEARCH strings containing paratheses for "ENO=" and ",SUB". I've tried changing the paratheses to CHR(34) as well but now it returns run-time error 1004 instead. Is there something I'm missing here?

*ORIGINAL*

Sub PastFormula()
Dim LRw As Long
Sheets("Sheet1").Range("S:S").Select
ActiveCell.Formula = "=MID(L2,(LEN(L2)-(LEN(LEFT(L2,LEN(L2)-SEARCH(“ENO=“,L2)))-4)),((LEN(LEFT(L2,LEN(L2)-SEARCH(“ENO=“,L2)))-4)-(LEN(LEFT(L2,LEN(L2)-SEARCH(“,SUB”,L2))))))"
LRw = Range ("L" & Rows.Count).End(xlUp).Row
Range(ActiveCell, Cells(LRw, ActiveCell.Column)).FillDown
End Sub

*Parantheses changed into CHR(34)*

Sub PastFormula()
Dim LRw As Long
Sheets("Sheet1").Range("S:S").Select
ActiveCell.Formula = "=MID(L2,(LEN(L2)-(LEN(LEFT(L2,LEN(L2)-SEARCH(CHR(34)&ENO=&CHR(34),L2)))-4)),((LEN(LEFT(L2,LEN(L2)-SEARCH(CHR(34)&ENO=&CHR(34),L2)))-4)-(LEN(LEFT(L2,LEN(L2)-SEARCH(CHR(34)&,SUB&CHR(34),L2))))))"
LRw = Range("L" & Rows.Count).End(xlUp).Row
Range(ActiveCell, Cells(LRw, ActiveCell.Column)).FillDown
End Sub

 

[Dossfm0q]

Sub PastFormula()
Dim LRw As Long
Sheets(1).Range("S:S").Select
ActiveCell.Formula = "=MID(L2,(LEN(L2)-(LEN(LEFT(L2,LEN(L2)-SEARCH(""ENO="",L2)))-4)),((LEN(LEFT(L2,LEN(L2)-SEARCH(""ENO="",L2)))-4)-(LEN(LEFT(L2,LEN(L2)-SEARCH(""SUB"",L2))))))"
LRw = Range("L" & Rows.Count).End(xlUp).Row
Range(ActiveCell, Cells(LRw, ActiveCell.Column)).FillDown
End Sub

 

[reubanrao93]

Hi there, thank you very much! Solved it.

 

[Dossfm0q]

You are welcome