VLookup/WorksheetFunktion not working

[moori]

Hi everyone,

I need a VLookup in my VBA code, but neither

Application.Vlookup

nor

Application.WorksheetFunction.Vlookup

works.

Neither of them appears in the Applications list, I get a "Method or data member not found" message, and the help files contains no related entries.
What am I missing?

What I'm trying to do is the following:
in a form, the user chooses an Excel file 1 containing product costs.
VBA creates an Excel file 2 with all products and opens the selected file 1.
Now I want for every product in 2 to look up the cost from 1.
I also tried a Macro, but it is not working with the VBA reference of 1.

Does anyone have an idea?

Saludos,
moori

 

[John Big Booty]

VLookup is a function particular to Excel, you will need to use one of the Domain Aggregate Functions. DLookup() in all probability.

 

[moori]

But I am working on Excel Sheets, not in Tables. Can I refer to a Excel file in DLookup?
I tried this too, but couln´t get the syntax right.
Something like:

xl2.cells(row, column)=DLOOKUP('[xl1.[Sheet 1]Sheet 1',column,lookup_value)

?

 

[John Big Booty]

Are we talking about Access or Excel here If you are working in Excel the VLookup() is still valid, if you are working in Access then not.

In Access Cell Addresses such as A2 have no meaning, as records can be sorted by any of their fields. Rows are referred to as Records and Columns as Fields. A record is generally identified by a field containing a unique identifier (RecordID), often a system generated Auto-number. So in Access instead of extracting data by it's cell address, you need to extract it by referring to a RecordID and the Field that contains the data you are interested in.

 

[Kiwiman]

Howzit

This is the syntax that has worked for me. You need to make sure you reference the excel library. I have a working example at home but I do not get home until Friday.

Re: intMth = WorksheetFunction.VLookup(strmth, myRange, 2, False)

 

[moori]

John, we are talking about a vlookup in Excel via VBA that is called by Access...
Kiwiman, nothing worked, so in the end I used a different approach: read my data into a temporary Access table and then fill the Excel Sheet via dlookup. Might not be very straight, but it works ;-)

Thanks for your input!
Btw, Kiwiman, I love your signature!! =D

 

[Kiwiman]

Howzit

Try the formulaR1C1 to populate excel using vlookup. In this case I

• Select the range
• Populate the range with a vlookup, using the iserror function in case of errors
• Copy the range
• Paste values - basically to keep the size of the file down

' Get some data using vlookup
    xl2.cells(row, column).Select   ' Select the range
        With Selection
            .FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],codes,2,FALSE)),"""",VLOOKUP(RC[-1],codes,2,FALSE))"    ' Populate range using vlookup incorporating iserror in case of errors
            .Copy    ' Copy range
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False     ' Paste values - to keep size down
        End With

This is an example of vba using the worksheetfunction from excel - I do have an example of one called from access somewhere but as yet I cannot find it.

' Assign a value to a variable (intMth) using the worksheetfunction, then use the variable to calculate a date for later use
    Set myRange = Worksheets("Report Month").Range("mthval")
    strmth = Sheets("Instructions").Range("mthsel")
    intYear = Sheets("Instructions").Range("yrsel")
    intMth = WorksheetFunction.VLookup(strmth, myRange, 2, False)
    dteDate = DateSerial(intYear, intMth + 1, 0)
    
    Set myRange = Nothing

 

[moori]

Thanks for your efforts!! I´ll try it next time - surely I will come across a similar problem soon!
Saludos de México!