Vba Macro Formula copied to a cell by range

lapot

New Member
Joined
Jul 25, 2014
Messages
27
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


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

 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

lapot

New Member
Joined
Jul 25, 2014
Messages
27
Try

Rich (BB code):
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top