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
kind regards
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
Code:
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