Can someone who's good with Excel VBA tell me what I am doing wrong with a simple lookup function? THANKS!!

To tell the whole story short, I am trying to write an advanced lookup function (not a Sub routine but a Function routine) using Excel VBA. I've already gotten most of the codes down, and it works fine.....but only as long as my function arguments run on the same worksheet as where the data being searched is on.

What I mean is that the function I write on one worksheet can't search for data range from another worksheet. I've tried multiple methods, even tried using the "Select" or "Activate" arguments to try to fool Excel thinking that I am running the program on the data range worksheet. But it didn't work :(

I think it must be because there's something wrong the way I am referring to ranges (I am a newbie). The routine I wrote include a code like this:

=vlookup(name, Worksheets(worksheetname).Range(Cells(x,y), Cells(m,n)), month, false)

Both "name" and "month" are variables. I feel the problem is the second part of the vlookup function. Can someone help me please?

1 Answer

  • 1 decade ago
    Best Answer

    In Excel VBA, vlookup has to be called like the following

    result=Application. WorksheetFunction. vlookup( name, Worksheets( worksheetname) .Range(Cells(x ,y), Cells(m ,n)), month , false)

    other than that probably we need to see more of your code.

    Hope this helps.

Still have questions? Get your answers by asking now.