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)
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
