Trouble with code containing nested vlookup

[mo4391]

Hello All,

I've been hunting and pecking the message board to try to develop some VBA code that will work for me. But, since I only know VBA well enough to be dangerous I'm running into problems. What I have so far is:

Please Login or Register  to view this content.

What I'm basically trying to accomplish is to have the formula copied in column I, beginning at I2 and ending at the last row where there is data. The error I'm getting is "Compile error: Expected End of Statement" and it highlights the first instance of the word Core in the formula line.

The formula is supposed to lookup the value in column F in the table I have named Workshop and return the value in the second column of that table. If it returns "Core/Subcore", it shows that value. If it does not return "Core/Subcore" then it looks up the value in column G in the table I have named Module and returns the value in the second column of that table. If no match is found it leave the cell blank. The formula works when I type it manually into the worksheet then copy and paste down. The purpose for going the VBA route is that each month I get a new data sheet and I don't want to have to copy and paste the formula each month.

Can anyone help me fix this?

Thank you in advance.

[simarui]

try this:

Sheets("Sunset_Query").Range("I2:I" & lr).FormulaR1C1 = _
"=IF(IFERROR(VLOOKUP(RC[-3],Workshop,2,FALSE),"""""""")=""Core/Subcore"",""Core/Subcore"", IFERROR(VLOOKUP(RC[-2],Module,2,FALSE),""""""""))"

though that macro alone is not likely to be faster than copying and pasting one formula from one workbook to another...

[mo4391]

Thanks for the quick response simarui. Your line was putting an " in the non-matching cells rather that leaving them blank. But I was able to figure out how to fix that one, so all works like a charm now. For what it's worth, this is actually much faster. Each month the new data is imported into the existing worksheet, so a simple keyboard shortcut for this macro and I'm done lickity-split.

Thanks again for your help.

[simarui]

well glad i could help. don't forget to mark it solved! and thanks for the rep!