VLookup across Multiple Sheets

Re: VLookup across Multiple Sheets - OZGrid UDF not working

Excel 2010 (only)
Consolidate multiple tabs with PivotTable
With macro and SumProduct()
http://c3017412.r12.cf0.rackcdn.com/04_03_11.xlsm
If you get *.zip, don't unzip, just rename *.xlsm

Re: VLookup across Multiple Sheets - OZGrid UDF not working

That's because it stops at the first match:

"In other words the UDF has the exact same syntax as Excels VLOOKUP. The only difference is that it will look in ALL Worksheets and stop at the first match. You can find the UDF (VLOOKAllSheets) in the Paste Function dialog (Shift+F3) within the "Function category" of "User Defined"."

You need to change the code to sum all the matches. Something like this (I haven't tested this code...you may need to debug it):

Dim wSheet As Worksheet


Dim vFound
Dim vFound1


On Error Resume Next


        vfound = 0


	For Each wSheet In ActiveWorkbook.Worksheets


		With wSheet


		Set Tble_Array = .Range(Tble_Array.Address)
                




			vFound1 = WorksheetFunction.VLookup _
			(Look_Value, Tble_Array, _
			Col_num, Range_look)


		End With


		If Not IsEmpty(vFound) Then


                vFound = Vfound + vFound1


	Next wSheet






	Set Tble_Array = Nothing


	VLOOKAllSheets = vFound

Re: VLookup across Multiple Sheets

to widgetwonka, your function is really good, I just needed to add a couple of things to get it to work:

Function VLookAllSheets1(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_Look As Boolean)
Dim wSheet As Worksheet
Dim vFound
Dim vFound1
On Error Resume Next
vFound = 0
For Each wSheet In ActiveWorkbook.Worksheets
vFound1 = 0
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound1 = WorksheetFunction.VLookup(Look_Value, Tble_Array, Col_num, Range_Look)
End With
If Not IsEmpty(vFound) Then
vFound = vFound + vFound1
End If
Next wSheet
Set Tble_Array = Nothing
VLookAllSheets1 = vFound
End Function

Notice I needed to end the if statement and also reset the variable vFound1 with each for statement.

HTH others as well.

Re: VLookup across Multiple Sheets

Thanks for cleaning it up Sierra. I didn't test it, so I am not surprised it needed some revision. You don't see a lot of first time posters with that level of debugging acumen. A big welcome from one Excel junky to another!

DJ,

If you go the PT route, GETPIVOTDATA is also a good option, since you can configure the lookup across multiple dimensions.

Re: VLookup across Multiple Sheets

sierrapension - please edit your post and include code tags around the code listing. These are required by the rules here. If needed, you'll find a link to code tag help in the relevant rule on the page http://www.ozgrid.com/forum/announcement.php?f=8

Re: VLookup across Multiple Sheets

cytop, thanks for the nudge and especially the link, I couldn't figure out how to do that. Fixed it.

Re: VLookup across Multiple Sheets

TY for the edit...

Re: VLookup across Multiple Sheets

Thanks Widget. This is actually my first excel function (I do have some coding background though). I had to do some research to figure out why it kept failing debugging, especially since the fail said there was a next with no for statement which obviously wasn't right.

Re: VLookup across Multiple Sheets

Sierra and WidgetWonka,

I'm trying to use the function, but I'm getting a "circular" error. I THINK it's because the worksheet I'm looking up from is in the same workbook as the sheets I'm looking from, so it's trying to look into that sheet as well.