VBA Inserting Formulas in to Cells

DaveRadford

Board Regular
Joined
Feb 18, 2010
Messages
63
Afternoon,

I have some code...which is very long winded but it has two issues.

Firstly when it copies the formula in to the cell it references the target cell with speech marks for example 'B12' meaning that teh forumula lookup doesnt work.

Second problem, as you can imagine as the selection happens yuo see it jumping through the cells one by one....not what i would like people to see :/

Here is my code.....please dont laugh :D, it will do this for all cells C11-C46.

Sub CopyJobFormulas()

Range("C11").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(B11,JobList,2,FALSE),""ERROR - This is not a valid Job Number"")"

Range("C12").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(B12,JobList,2,FALSE),""ERROR - This is not a valid Job Number"")"

Range("C13").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(B13,JobList,2,FALSE),""ERROR - This is not a valid Job Number"")"

End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub CopyJobFormulas()
Range("C11:C13").Formula = "=IFERROR(VLOOKUP(B11,JobList,2,FALSE),""ERROR - This is not a valid Job Number"")"
End Sub
 

DaveRadford

Board Regular
Joined
Feb 18, 2010
Messages
63
Thanks for the reply, i should have explained better, the range for the lookup also changes each time. So B11 would be fine for C11 however when the range moves to C12 ill need it to do a lookup on b12.

Thanks,
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Excel will adjust the formulas accordingly - try it.
 

Forum statistics

Threads
1,136,340
Messages
5,675,202
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top