VLOOKUP problem

[Euler271]

I'm in Access VBA trying to create and manipulate an Excel workbook. I fill two worksheets and need to use the Excel VLOOKUP formula in one worksheet to lookup values in the other worksheet. I need to fill a column of cells with the VLOOKUP function but when the code reaches the line:



xlWs.Range("O2:O" & xlWs.Cells.SpecialCells(xlLastCell).Row).FormulaR1 C1 = "=VLOOKUP(P2,OK!$G$2:$G$" & LastStateRow & ",1,FALSE)"

I get a 1004 error "Application-defined or object-defined error".

The following previous line:

xlWs.Range("O2:O" & xlWs.Cells.SpecialCells(xlLastCell).Row).FormulaR1 C1 = "=RC[1]&RC[-12]"

works just fine.

I read that VBA doesn't like #N/A and that might be what's going on here. My question is how can I insert the VLOOKUP formula in the column cells even if VBA encounters #N/A, if that's the problem.

Thanks.

[JoeM]

The second one is using R1C1 range referencing. The first one it not.

So change it to:

xlWs.Range("O2:O" & xlWs.Cells.SpecialCells(xlLastCell).Row).Formula = "=VLOOKUP(P2,OK!$G$2:$G$" & LastStateRow & ",1,FALSE)"

[Euler271]

I should've mentioned that I tried it with R1C1 referencing:

xlWs.Range("O2:O" & xlWs.Cells.SpecialCells(xlLastCell).Row).FormulaR1 C1 = "=VLOOKUP(RC[1],OK!$R$C[-8]:$R[" & LastStateRow & "]$C[-8],1,FALSE)"

with the same result.

[JoeM]

Try incorporating the Excel IFERROR formula into your equation, which tells Excel what to return instead of an error.
See: https://exceljet.net/formula/vlookup-without-na-error

[Euler271]

That's great. I'll do just that. Thanks for responding.

[JoeM]

You are welcome.

[Missinglinq]

Have you set a reference for the Excel library?

This might help...for Access 2007 (which is also good for v2010, I believe)

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

Linq ;0)>

[Euler271]

Thanks for replying.

I haven't read the entire article but it appears to be about asking Excel to do a calculation and then returning the result to Access. That's not what I'm trying to do in this case.