98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » excel+training - VLookup
excel+training - VLookup
Resolved · Low Priority · Version Standard
Wed 12 Dec 2007, 11:42Delegate Jennifer said...
Jennifer has attended:
Excel VBA Intro Intermediate course
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)
Fri 4 Jan 2008, 10:51
Edited on Fri 4 Jan 2008, 10:52Trainer Stephen said...
RE: VLookup
Hi Jennifer
Thankyou 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
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 tip:Fill formulae across a sheetTo copy a formula down a spreadsheet where there is data underneath, to the left or to the right of the formula, double-click on the fill handle. The fill handle is the little black cross that appears in the bottom right-hand corner of the formula cell. Unfortunately, no similar facility exists to copy formulae across the sheet. |
