Macro for Lookup

[macdca]

Can anyone tell me how to write a macro for a VLOOK up procedure, lining to a worksheet which looks up over 2 worksheets?

Thanks

 

[VoG]

Perhaps you can adapt this http://www.ozgrid.com/VBA/VlookupAllSheets.htm

 

[macdca]

How does it link with the look u sheet? Do I have to have them both open at the same time? I hae pasted the sheet in and the formulas but cant get it to run. The example has range =VLOOKAllSheets("Dog",C1:E20,2,FALSE)
what wrksheet is this pointing to for the look up, who does it go from C to E20? My range is up to 30000 rows and the look up value is in column e of my datasheet and column A of the look up sheet??

 

[macdca]

basically I am looking up values in column E of the active workbook against sheet 1, sheet 2 and sheet 3 in another look up workbook saved somewhere?

 

[VoG]

It assumes that the sheets are all in the same open workbook.

 

[macdca]

OK, i have copied the sheets in and called them 1, 2 and 3
=VLOOKAllSheets("Post Code",E2:G6500,2,FALSE) is the formula I have entered and I am getting compile error?
E is the post code field/column on my main sheet, and I only have columns A to G?

 

[VoG]

What compile error and which line of code is causing the error?

 

[macdca]

I am getting invalid outside procedure, and the line highlighted below is the one with the problem?


'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

 

[VoG]

You haven't got all the code there. It should be

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, Col_num As Integer, Optional Range_look As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com
'Use VLOOKUP to Look across ALL Worksheets and stops at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound
On Error Resume Next
    For Each wSheet In ActiveWorkbook.Worksheets
        With wSheet
        Set Tble_Array = .Range(Tble_Array.Address)
            vFound = WorksheetFunction.VLookup _
            (Look_Value, Tble_Array, _
            Col_num, Range_look)
        End With
        If Not IsEmpty(vFound) Then Exit For
    Next wSheet
    Set Tble_Array = Nothing
    VLOOKAllSheets = vFound
End Function

 

[macdca]

I have used this in the project now and used the formula =VLOOKAllSheets("Post Code",E2:G6500,2,FALSE)

I am getting 0 in each field. Im not clear whhat the E and G ranges are representing here? Also, I would like it to run as a macro rather than pasting in a formuls if thats possible?