Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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. sharonm wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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("A2:D15") ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.O ffset(0, -31).Value, rng, 3, False) -- Regards, Tom Ogilvy "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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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("A2:D15") ActiveCell.Value = _ Application.WorksheetFunction. _ VLookup(ActiveCell.Offset(0, -31).Value, rng, 3, False) Best Regards, Walt Weber sharonm wrote: 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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
Thanks to all. But now when on the line with the lookup-
ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.O ffset(0, -31).Value, rng, 3) I get the error 1004: Application-defined or Object-defined error Any Suggestions? "Dave Peterson" wrote: 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. sharonm wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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.O ffset(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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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 -- Regards, Tom Ogilvy "sharonm" wrote in message ... Thanks to all. But now when on the line with the lookup- ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.O ffset(0, -31).Value, rng, 3) I get the error 1004: Application-defined or Object-defined error Any Suggestions? "Dave Peterson" wrote: 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. sharonm wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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("A2:D15") That workbook has to be open. "L. Howard Kittle" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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!). Walt wrote: 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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. -- Regards, Tom Ogilvy "Walt" wrote in message ups.com... 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) |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in vba code
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP as a vb code | Excel Discussion (Misc queries) | |||
Using VLOOKUP in VBA code | Excel Worksheet Functions | |||
what is the VBA code for VLOOKUP? | Excel Programming | |||
Using Vlookup in VBA code | Excel Programming | |||
Help with Vlookup code | Excel Programming |