Vba Macro Formula copied to a cell by range

[lapot]

Dear all
I am try to insert this formula "=MID(E2,FIND(""Bill ref"",E2,1) + 8, 7) & MID(E3, LEN(E2)-0,0)" with below code into B2 which runs as long as the data range on Column E


When the formula entered it becomes "=MID('E2',FIND("Bill ref",'E2',1) + 8, 7) & MID('E3', LEN('E2')-0,0)" and gives #NAME? error this is because cell names get ‘’ added as in ‘E2’ , when I cleared them then formula works. Do you know why this happens also formula cell reference doesn’t change as it was copied down. Can this be turn into variable For example E2,E3,E4,E5….. etc

Sub CopyFormula()
Dim lngLastRow As Long 'declare a variable for the last row
lngLastRow = Range("E" & Rows.Count).End(xlUp).Row
Range("B2:B" & lngLastRow).FormulaR1C1 = "=MID(E2,FIND(""Bill ref"",E2,1) + 8, 7) & MID(E3, LEN(E2)-0,0)"
End Sub

kind regards

 

[VoG]

Try

Range("B2:B" & lngLastRow).Formula = "=MID(E2,FIND(""Bill ref"",E2,1) + 8, 7) & MID(E3, LEN(E2)-0,0)"

 

[lapot]

Try

Range("B2:B" & lngLastRow).Formula = "=MID(E2,FIND(""Bill ref"",E2,1) + 8, 7) & MID(E3, LEN(E2)-0,0)"

thank you so much, it worked, you are a star.