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 wrote:

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

Tom Ogilvy wrote:
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.

--
Regards,
Tom Ogilvy

"L. Howard Kittle" wrote in message
...
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

"sharonm" wrote in message
...
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("A2:D15")

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




--

Dave Peterson