Creating formula with VBA

[gt93grad]

I need to apply this formula via VBA code, but I'm not having much luck. This is the formula: =IF(ISERROR(VLOOKUP(D7,SparesOriginsLookup,4,FALSE)),"HKN1",VLOOKUP(D7,SparesOriginsLookup,4,FALSE))

I'm fine with using D7, or RC[-1], but I can't get the quotes right when apply within the code. If the VLOOKUP works, apply the value. If not, set HKN1. Can someone help here?

I see examples online, but this is tough to recreate.

 

[shg]

It would be much easier if you posted the code that doesn't work, but lacking that, double up the quotes.

 

[gt93grad]

It would be much easier if you posted the code that doesn't work, but lacking that, double up the quotes.

Here's what I have. The first section is attempting to use RC, and second is attempting to use D7.

ActiveCell.FormulaR1C1 = "=""IF(ISERROR(VLOOKUP("" & RC[-1] & "",SparesOriginsLookup,4,FALSE)),""HKN1"",VLOOKUP("" & D7 & "",SparesOriginsLookup,4,FALSE))"

 

[mikerickson]

You could use the macro recorder to record entering the formula into a cell, that would give you properly syntaxed VB code.

 

[gt93grad]

Oh yeah. Forgot the easiest way. That works great. Thanks. Here's the answer for others:

ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE)),""HKN1"",VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE))"

 

[gt93grad]

Oh yeah. Forgot the easiest way. That works great. Thanks. Here's the answer for others:

ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE)),""HKN1"",VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE))"

Spoke too soon. Now it's putting the formula in the cell as a literal, and not resolving it. I changed the type to General, clicked in the field, and hit enter again, but no luck.

 

[gt93grad]

Never mind. The range hadn't been created properly, so that was the problem. All set.

 

[gt93grad]

Nope, never mind. It's still inserting as a literal. Let me know if you can see anything wrong.

ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE)),""HKN1"",VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE))"

 

[shg]

Does it reference a cell formatted as Text? If so, change the format of the cell containing the formula to something other than General, e.g., Number.

Also, you might simplify to

... = "=IFERROR(VLOOKUP(RC[-1], SparesOriginsLookup, 4, FALSE), ""HKN1"")"

 

[gt93grad]

Here's a similar problem, but I can't get this one to work either. "StageCostRange" is a variable. I can't figure out where the quotations and apostrophes should go.

ActiveCell.FormulaR1C1 = "=IFERROR('VLOOKUP(A7&B7, " & StageCostRange & " ,2,FALSE)',0)"