CODEWITHSUNDEEP
vbaexcel
dataminer123
dataminer123

Reputation: 65

search for multiple values - loop

I think this can only be done in VBA. I tried VLOOKUP, but no luck.

I want to search a whole sheet for certain values. These values come from a row in another sheet. Once each value is found, it looks to the top most row and pulls that value.

I might need to loop through each value in the row and search the sheet from there?

my thought/example:

Sheet1!A:A
1234
5325
6346
6342

look in sheet 2 for 1234. lets say its found in cell G5, it will then look at the value in G1 and input that into cell A1 on sheet 2. I'm sorry for making this really confusing.

Here's what I have starting out:

Sub FindValues()

Dim SearchRow As String
Dim SearchRange As Range, cl As Range
Dim FirstFound As String
Dim sh As Worksheet

' Set Search value
SearchRow = Sheets("sheet2").Range("B:B")

getting error on the last line. run-time error '13': type mismatch

Upvotes: 1

Views: 672

Answers (2)

nbayly
nbayly

Reputation: 2167

Please consider the following code for your requirements:

Sub FindValues()
    Dim SearchRow As Range
    Dim SearchRange As Range
    Dim Cell As Range
    Dim FirstFound As Range

    ' Set Search value
    Set SearchRow = Sheets("Sheet1").Range("B:B")
    Set SearchRange = Sheets("Sheet2").Range("A:K")

    For Each Cell In SearchRow
        Set FirstFound = SearchRange.Find(Cell.Value2)
        If Not FirstFound Is Nothing Then
            Cell.Offset(0, 1).Value2 = SearchRange.Cells(1, FirstFound.Column).Value2
        Else
            Cell.Offset(0, 1).Value2 = "No Value Found"
        End If
    Next
End Sub

Note that as per your description I assumed that the SearchRange was more than just 1 column.

Basically the script loops through each Cell in the SearchRow and looks for the value in the SearchRange. If it finds the value and returns a subrange representing cells with the search value and sets the value to the right of each Cell to the value of the top cell of the column where the value was found. Hope this serves your needs. Cheers.

Upvotes: 2

user4039065
user4039065

Reputation:

You need to Set a Range object. You cannot assign it to a string unless you are looking for a property that is a string like the Address property.

Dim SearchRow As String
Dim SearchRange As Range

SearchRow = Sheets("sheet2").Range("B:B").address
Set SearchRange = Sheets("sheet2").Range("B:B")

Upvotes: 2

Related Questions