vlookup in vba code

[Guest]

Hello,
I am trying to use Vlookup in Excel VBA. I have two sheets in my workbook
which is called "Validation Macro.xls" . I want to do a lookup based on a
cell's value in the first sheet. The lookup range is in the second sheet
called "TLA". I am trying to do this with the code below. Only the first line
gives me the following error: "Runtime error 9 - Subscript out of range".
Would anyone have any suggesions on how to properly do this? Thanks in
advance!


Set wb = Workbooks("C:\Documents and Settings\Sharon\MyDocuments\Validation
Macro.xls")
Set ws = wb.Sheets("TLA")
Set rng = ws.Range("A215")

ActiveCell.Value =
Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, -31).Value, rng,
3, False)

 

[Dave Peterson]

When you refer to a workbook with:

Workbooks("workbooknamehere.xls")...

You don't include the path. So in your case:

set wb = workbooks("validation macro.xls")

And that workbook has to be open, too.

 

[Tom Ogilvy]

You can't do a lookup on a closed workbook, so assuming Validation Macro.xls
is open

Set wb = Workbooks("Validation Macro.xls")
Set ws = wb.Sheets("TLA")
Set rng = ws.Range("A215")

ActiveCell.Value =
Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, -31).Value, rng,
3, False)

 

[Walt]

Hi sharonm,

If your activecell is in any column less than 32(AF), you would get
that error, since you are referenceing a cell 31 columns to the left of
the activecell.

Also, try:
Set rng = thisworkbook.worksheets("TLA").Range("A215")
ActiveCell.Value = _
Application.WorksheetFunction. _
VLookup(ActiveCell.Offset(0, -31).Value, rng, 3, False)

Best Regards,
Walt Weber

 

[Guest]

Thanks to all. But now when on the line with the lookup-

ActiveCell.Value =
Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, -31).Value, rng, 3)

I get the error 1004: Application-defined or Object-defined error

Any Suggestions?

 

[Walt]

Hi sharonm,

You didn't show that you modified the 'Set' statement. How do you
have that set at this point?

Also, I noticed in line containing the Vlookup function

"ActiveCell.Value =
Application.WorksheetFunction.VLookup(ActiveCell.Offset(0, -31).Value,
rng, 3)"

you don't include the line continuation character '_' after the '='
sign. Is this just a word wrap in what I'm seeing or does it show as
two lines in your VBA editor screen?

I do note that you dropped the ',False' parameter for the Vlookup
function. If that's intentional, that's OK, it's optional, but if
there is no direct hit on the lookup, the result will be different.

Best Regards,
Walt Weber

 

[Tom Ogilvy]

That means the value wasn't found in range. One approach is to skip the
worksheetfunction part. Then you can check with iserror

res = Application.Vlookup(ActiveCell.Offset(0,-31).value,rng,3,False)
if iserror(res) then
msgbox ActiveCell.offset(0,-31).Value & " was not found"
else
ActiveCell.Value = res
end if

 

[L. Howard Kittle]

Hi Tom,

<You can't do a lookup on a closed workbook, so assuming Validation
Macro.xls
is open

I must be missing something with this comment, or I am taking it out of
context, because I have a lookup array in a closed workbook and the lookup
formula in an open workbook and it works fine.

I am way aware of your expertise so I submit this as a dumb question on my
part not a challenge to your advise. Maybe be a VBA macro limitation
perhaps??

Regards,
Howard

 

[Dave Peterson]

I think it's the context stuff...

For the =vlookup() to work in code, the workbook has to be open.

Even more,

Set rng = workbooks(xxx).worksheets(yyy).Range("A215")

That workbook has to be open.

 

[Tom Ogilvy]

Hello Howard,

The original question began:

I am trying to use Vlookup in Excel VBA.

so, yes, it is a VBA macro limitation

You are most correct that formulas use in cells, for the most part, support
links to closed workbooks and I had not intention of saying that isn't the
case.

 

[Walt]

Hi Tom,

How did we get on to this issue of open or closed or even multiple
workbooks? From the original question I read "I have two sheets in my
workbook which is called "Validation Macro.xls" ." and I inferred the
workbook would be open since the VBA functioning was in question and
that the only issue had to do with a lookup between 2 worksheets in the
same workbook. Isn't the original question having to do with 2
worksheets in 1 workbook?

Best Regards,
Walt Weber

 

[Dave Peterson]

I'm not Tom, but I skipped over that portion of the post.

When I saw:

Set wb = _
Workbooks("C:\Documents and Settings\Sharon\MyDocuments\Validation Macro.xls")

I just assumed (incorrectly) that the OP wanted to use a different workbook.

On the other hand, I don't see anything explicit that says that the code is
actually in the same workbook as the worksheets (a bit of a stretch, maybe
<bg>).

(On the third hand, I could have skipped over that part again!)

 

[Walt]

Hi Dave,

I almost did the same, and it might have been that same line that had
me started that way. It was after I'd roughed out a response and
wanted to be certain I hadn't missed any points in the query that I
caught myself and did a quick re-write.

Agreed, the code could be in another workbook and still fit the query
as posed.

Best Regards,
Walt Weber

 

[Dave Peterson]

I often respond to the question I read--not to the question that was written
<vbg>.

It makes life much more interesting (to me anyway!).

 

[Tom Ogilvy]

all I did was state that you can't use vlookup with a closed workbook which
the OP illustrated as a possibility with the code they presented and you
and Dave have again cited.

I next said, "assuming the workbook is open".

So beyond the cautionary statement, I believe I provided answers to the
questions asked.

 

[Walt]

Hi Tom,

Two days later, I guess we can take silence as an affirmative that the
original poster has a functioning result (My concern was unnecessary).

I look forward to reading more of your posts and considering the ideas
they trigger. Thanks Tom.

Best Regards,
Walt Weber