VBA search all worksheets for a value and return corresponding value from another column

[jacinthn]

i have been stuck on this for the last 3 days :'( im trying to find a value in column A:A starting with A2 and search for a match in column A across all the workshets in the active workbook (the number of work sheets vary depending on the wb) and when the value is found on whichever worksheet return the corresponding value from column K, i cant seem to make the code work and ive tried a couple different ways over the last few days, nothing is working please help :'(


Sub search()
Dim wkb As Workbook
Dim ws As Worksheet
Dim Cell As Range
Dim FoundCell As Range

On Error Resume Next
Set Cell = Range("a2").Value

With wkb
For Each ws In wkb.Worksheets
Set FoundCell = ws.Cells.Find(what:=Cell.Value, _
LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
If Not FoundCell Is Nothing Then
Cell.Offset(, 10).Value = FoundCell.Row
Exit Sub
End If
Next ws
On Error GoTo 0
End With
End Sub

 

[VoG]

Try VlookAllSheets http://www.google.co.uk/url?sa=t&so...9IiEBQ&usg=AFQjCNHzRASUZ82f68bjuoKS_2hQL6bgmg

 

[jacinthn]

Thanks for the reply i had tried that and it didnt seem to work
this was the original code on the link and i updated line
Set Tble_Array = .Range(Tble_Array.Address) to Set Tble_Array = .Range("A2:A)
but noting comes back when i run it. and i cant figure out what im doing wrong

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 Sub

 

[VoG]

Your table array must include columns A:K and col_num must be 11.

 

[jacinthn]

im just starting to learn vba did you mean like this?
Set Col_num = 11
Set Range_look = Range("A2:A")
Set Tble_Array = .Range("A:K")
Set Look_Value = Range("A2").Value



Function VLOOKAllSheets()
Dim Look_Value As Variant
Dim Tble_Array As Range
Dim Col_num As Integer
Dim OptionalRange_look As Boolean

Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Col_num = 11
Set Range_look = Range("A2:A")
Set Tble_Array = .Range("A:K")
Set Look_Value = Range("a2").Value
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

 

[jacinthn]

got it, thanks for all your help