excel+training - VLookup

Resolved · Low Priority · Version Standard

Wed 12 Dec 2007, 11:42 Delegate 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:52 Trainer 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 courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Fill formulae across a sheet

To 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.

One reasonably quick way to copy an existing formula across a sheet is to select the formula and the cells on the right to which you want to copy it. Then press Ctrl+R to copy the formula across the selected range, or, if you are menu-minded, use the Edit|Fill|Right command.

View all Excel hints and tips