3

I am getting runtime error 1004 when trying to insert a formula into a cell in VBA.

In my excel sheet I have a date in column A and a stock ticker in row 1 starting in column B every 3 columns, so B, E, H etc.

In cell C2 I am trying to divide the value in B2 by the value in row 2 under the column heading "FTSE". Putting this formula into the cell directly works:

=IFERROR(B2/(VLOOKUP($A2,$A$2:$GMQ$261,MATCH("FTSE",$B$1:$GMQ$1,0)+1,FALSE)),"")

I am trying to do this using vba. This is the code I have:

Sub InsertFormula()

   Range("C2").Select
   ActiveCell.Formula = _
   "=IFERROR(B2/(VLOOKUP($A2,$A$2:$GMQ$261,MATCH(""FTSE"",$B$1:$GMQ$1,0)+1,FALSE)),"")"

End Sub
J.Fleming
  • 31
  • 2
  • 1
    Remember to "double-up" on that final pair of double-quotes! – Gary's Student Apr 17 '16 at 13:19
  • gary is right... the formula needs to be `"=IFERROR(B2/(VLOOKUP($A2,$A$2:$GMQ$261,MATCH(""FTSE"",$B$1:$GMQ$1,0)+1,FALSE)),"""")"` (you missed the "" for the error-part to double up) – Dirk Reichel Apr 17 '16 at 13:21
  • @Ralph I posted it as a comment because I was using a cell phone and could not completely test an alternative line of code. – Gary's Student Apr 17 '16 at 15:07

1 Answers1

3

You can reduce the confusion generated by double-double-quotes¹ with the TEXT function used as TEXT(,). This returns a zero-length string just as "" does and there are no quotes to double up like """".

Range("C2").Formula = _
 "=IFERROR(B2/(VLOOKUP($A2, $A$2:$GMQ$261, MATCH(""FTSE"", $B$1:$GMQ$1 ,0)+1, FALSE)), TEXT(,))"
'without the offset and subsequent adjustment with full column references
Range("C2").Formula = _
 "=IFERROR(B2/VLOOKUP($A2, $A:$GMQ, MATCH(""FTSE"", $1:$1 ,0), FALSE), TEXT(,))"

¹ See How to create strings containing double quotes in Excel formulas? for more examples.

Community
  • 1
  • 1