VBA Inserting Formulas in to Cells

[DaveRadford]

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

 

[VoG]

Try

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

 

[DaveRadford]

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]

Excel will adjust the formulas accordingly - try it.