Automating Search/Find in VBA

[Pobek]

Imagine a Workbook with 5 Sheets. Each sheet has different countries in col A, the corresponding Capital City in B and say the population in C.

I am trying to code the use of FIND in VBA such that for any given list countries:

for i = 1 to no of countries to investigate

-search the entire workbook for country i
- if country i is found in the list
- bring in its associated capital and population in the next column of the same row of the country in question
- If not next i

next i

I find that the display of the find result restricts any form of loop action to take place hence intervening the macro on each iteration. Any suggestion?

 

[BarryL]

what about a UDF to vlookup the results? lets say you put in the country on A2 in sheet1 and wanted to search sheet2 to sheet5the formula would be in B2:


=udfWsVlookup(A2,A:B,2,FALSE,"Sheet2","Sheet5")

Cant't remember who to give the credit to for this UDF but it isn't mine

Public Function udfWsVlookup(rLV As Range, rTA As Range, lCI As Long, bRL As Boolean, ws1 As String, ws2 As String) As Variant
    Application.Volatile
    Dim w As Long, v As Variant
    udfWsVlookup = ""
    For w = Sheets(ws1).Index To Sheets(ws2).Index
        If Not IsError(Application.VLookup(rLV, Sheets(w).Range(rTA.Address), lCI, bRL)) Then
            v = Application.VLookup(rLV, Sheets(w).Range(rTA.Address), lCI, bRL)
            If Len(v) > 0 Then
                udfWsVlookup = v
                Exit For
            End If
        End If
    Next w
End Function

 

[Pobek]

what about a UDF to vlookup the results? lets say you put in the country on A2 in sheet1 and wanted to search sheet2 to sheet5the formula would be in B2:


=udfWsVlookup(A2,A:B,2,FALSE,"Sheet2","Sheet5")

Cant't remember who to give the credit to for this UDF but it isn't mine

Public Function udfWsVlookup(rLV As Range, rTA As Range, lCI As Long, bRL As Boolean, ws1 As String, ws2 As String) As Variant
    Application.Volatile
    Dim w As Long, v As Variant
    udfWsVlookup = ""
    For w = Sheets(ws1).Index To Sheets(ws2).Index
        If Not IsError(Application.VLookup(rLV, Sheets(w).Range(rTA.Address), lCI, bRL)) Then
            v = Application.VLookup(rLV, Sheets(w).Range(rTA.Address), lCI, bRL)
            If Len(v) > 0 Then
                udfWsVlookup = v
                Exit For
            End If
        End If
    Next w
End Function

Hi,

Thanks for that... I am away from the workbook at the miute but will certainly try and get back to you. Many thanks