CurrentRegion Property Problem

[Pelusa]

I am following the Excel 2007 VBA Programmer's Reference book and stumbled into a problem.

Please Login or Register  to view this content.

The line that is bold and underlined is the one that is highlighted when I try to run this code.

I attached the worksheet as well.

The code is supposed to add all the values in each table and display them in the new "Consolidation" worksheet.

Not sure what is going wrong. Any help would be greatly appreciated.

[Andy Pope]

The code uses named ranges, such as "mangoes". Your file does not include any named ranges so the code fails.

[Pelusa]

Fixed it with:

Please Login or Register  to view this content.

Is there a way get the same effect using the vlookup function?

I am just wondering since I am still learning VBA.

[Andy Pope]

If you alter the name range defintions then yes.

Please Login or Register  to view this content.

Formula in B4 copy over and down.

Consolidation!B4: =SUM(VLOOKUP($A4,Mangoes,COLUMN(),FALSE),VLOOKUP($A4,Bananas,COLUMN(),FALSE),VLOOKUP($A4,Lychees,COLUMN(),FALSE),VLOOKUP($A4,Rambutan,COLUMN(),FALSE))