0

I'm making a bot that erases certain columns and fills them in with new data. By erasing the data, the formula becomes

=IFERROR(INDEX('AP query'!O:O,MATCH(Findings!#REF!,'AP query'!L:L,0)),"Non PO")

So I tried making a macro and filling the cell with the formula but it says there's an error. Is there a fix or another way to enter the formula into the the cell using macros?

Range("B3").Formula = "=IFERROR(INDEX('AP query'!O:O,MATCH(Findings!C3,'AP query'!L:L,0)),"Non PO")"
srcWorkbook.Worksheets("Findings").Range("B3").Copy
srcWorkbook.Worksheets("Findings").Range("B3:B" & LR).PasteSpecial xlPasteFormulas
braX
  • 11,506
  • 5
  • 20
  • 33
Kat
  • 1
  • 3

1 Answers1

1

When you wish to use a string in a formula =IF(A1="Hi",... in VBA, just "double up" the quotes:

Range("B3").Formula = "=IFERROR(INDEX('AP query'!O:O,MATCH(Findings!C3,'AP query'!L:L,0)),""Non PO"")"

Because, as VBA compiles that line, it'll hit the first quote at "Non Po" and think that's the end of the line. So (...trying to visualize it), it'd be reading that line like your formula is:

=IFERROR(INDEX('AP query'!O:O,MATCH(Findings!C3,'AP query'!L:L,0)),

Tangiental tip - sometimes when I hit similar issues, even just pasting the code here on SO, and formatting it as code, helps because as you can see in your post, the Non PO is a different color, but all formulas (strings maybe?) should be the same (all red, as in the answer I posted above).

BruceWayne
  • 22,923
  • 15
  • 65
  • 110