PDA

View Full Version : [SOLVED] VBA code for vlookup in multiple worksheets



9!GR@BzyQ37b
03-06-2020, 03:57 AM
Hi,

VBA is new for me and I searching for a solution, but I could find anything yet. Maybe someone would help me.

I would like to use vlookup in vba and the problem is that the vlookup has to search in multiple worksheets. And the worksheets could be variable. So sometimes I have 15 worksheets and sometimes 25.

This is want I want:
On the last worksheet I have in column A een range of numbers. And I want to search a specific value that belongs to one of these numbers. But this values is standing on one of the worksheets.

I've attached an example, so it helps you to understood me.

Thanks for your help!
Costertje

Paul_Hossler
03-06-2020, 05:12 AM
one way


26119




Option Explicit


Function SuperVLookup(v As Variant, r As String, n As Long) As Variant
Dim ws As Worksheet
Dim v1 As Variant


For Each ws In Worksheets
If ws.Name <> ActiveSheet.Name Then
On Error Resume Next
v1 = Application.WorksheetFunction.VLookup(v, Range("'" & ws.Name & "'!" & r), 2, False)
On Error GoTo 0

If Not IsError(v1) Then
If Len(v1) > 0 Then
SuperVLookup = v1
Exit Function
End If
End If
End If
Next


SuperVLookup = CVErr(xlErrNA)

End Function

9!GR@BzyQ37b
03-10-2020, 05:06 AM
Hi Paul,

Thank you for this solution. I can use it for this example, but also for other similar examples.

Thanks,
Costertje