Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » excel+training - VLookup
|
|
|
Bookmark this page|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
excel+training - VLookup
Resolved · Low Priority · Version Standard
Reply
VLookup
I am trying to transfer the value for a total for each row entry (rowentryname used below as eg) from a pivot table to summary sheet. The size of the pivot table (number of columns) may be different each time. I have set up finalrow and finalcolumn variables, which seem to work, but the last section of the code with the VLookup function doesn't work, bringing up error 1004 (Application-defined or object-defined error)FinalRow = Workbooks("workbookname.xls").Worksheets("worksheetname").Cells(Application.Rows.Count, 2).End(xlUp).Row
FinalColumn = Workbooks("workbookname.xls").Worksheets("worksheetname").Cells(8, Application.Columns.Count).End(xlToLeft).Column
Worksheets("wbname").Range("C7") = WorksheetFunction.VLookup("rowentryname", (Worksheets("worksheetname").Range(Cells(2, 8), Cells(FinalRow, FinalColumn))), FinalColumn - 1, False)
For upcoming training course dates see: Pricing & availability
RE: VLookup
Hi JenniferThankyou for your question
Near as I can tell, your problem seems to be with the syntax of your Vlookup function, specifically with the way in which the range of the lookup table (Second argument) is defined.
The easiest method is to name the range of the lookup table and then use the name in the code. I have replaced that line of code in your sample with the following and it seems to work fine
Worksheets("pivot").Range("F10") = WorksheetFunction.VLookup("Jan", Range("Table"), FinalColumn, False)I have named the range "table" and in this instance manually defined it. I appreciate that your problem is that the range needs to vary. I would suggest that you write a module that automatically determines the range of the table and then names it before you run the above code. There are a number of different possible methods using for example the "resize" or "Current region" properties.
If you have difficulties here I would be glad to help, but it would be handy to have an example of a typical workbook to base it on. If this is necessary and possible could you email it to
infoATmicrosofttrainingDOTnet
marking the subject as "for the attention of stephen"
Hope this helps
Stephen
|
 |
|
» Forum post: Shortcuts |
Where can I get a list of comprehensive Excel shortcuts? 
