Vba: look for a value scanning a row

[Nelson78]

Hello everybody.

I have the range A1:A8 of sheet1 with a series of values.

In sheet2 I have a lot of rows, from B2:Z2 to B100:Z100

Scanning any single row from B to Z, I need to report in column AA which is the first of the values in sheet1 range A1:A8 that I meet (if any).

Can I have any suggestions?

Thank you.

 

[Nelson78]

I think the logic is to look on each row for the first value that's in the list on sheet 1. Like this:

Public Sub FindByRow()


Dim thisRow As Long
Dim foundCol As Variant
Dim searchVal As Long
Dim minCol As Long


For thisRow = 2 To 100
    minCol = 99
    For searchVal = 1 To 8
        foundCol = Application.Match(Sheets("Sheet1").Cells(searchVal, "A").Value, Sheets("Sheet2").Range("B" & thisRow & ":Z" & thisRow), 0)
        If Not IsError(foundCol) Then
            If foundCol < minCol Then minCol = foundCol
        End If
    Next searchVal
    Sheets("Sheet2").Cells(thisRow, "AA").Value = IIf(minCol = 99, "Not found", Sheets("Sheet2").Cells(thisRow, minCol + 1).Value)
Next thisRow


End Sub

WBD

Yes, the logic is exactly that one.

Some tests I have just done are positive.

Thank you.

 

[wideboydixon]

You're welcome. The formula is a simpler solution though ...

WBD

 

[Nelson78]

I think the logic is to look on each row for the first value that's in the list on sheet 1. Like this:

Public Sub FindByRow()


Dim thisRow As Long
Dim foundCol As Variant
Dim searchVal As Long
Dim minCol As Long


For thisRow = 2 To 100
    minCol = 99
    For searchVal = 1 To 8
        foundCol = Application.Match(Sheets("Sheet1").Cells(searchVal, "A").Value, Sheets("Sheet2").Range("B" & thisRow & ":Z" & thisRow), 0)
        If Not IsError(foundCol) Then
            If foundCol < minCol Then minCol = foundCol
        End If
    Next searchVal
    Sheets("Sheet2").Cells(thisRow, "AA").Value = IIf(minCol = 99, "Not found", Sheets("Sheet2").Cells(thisRow, minCol + 1).Value)
Next thisRow


End Sub

WBD

I have some trouble in adapting your solution to my real situation.
For example, what is minCol?

 

[Nelson78]

In particular, I bump into an error when I try to change the quantity of rows to scan (in effect they are 42586, not 100).
If I leave 100, it works perfectly, but just until row number 100.

Public Sub FindByRow()


Dim thisRow As Long
Dim foundCol As Variant
Dim searchVal As Long
Dim minCol As Long


For thisRow = 2 To 42586
    minCol = 42585
    For searchVal = 1 To 8
        foundCol = Application.Match(Sheets("Sheet1").Cells(searchVal, "A").Value, Sheets("Sheet2").Range("B" & thisRow & ":Z" & thisRow), 0)
        If Not IsError(foundCol) Then
            If foundCol < minCol Then minCol = foundCol
        End If
    Next searchVal
    Sheets("Sheet2").Cells(thisRow, "AA").Value = IIf(minCol = 42585, "Not found", Sheets("Sheet2").Cells(thisRow, minCol + 1).Value)
Next thisRow


End Sub

With a Run-time error 104 - Application-defined or object-defined error on row

Sheets("Sheet2").Cells(thisRow, "AA").Value = IIf(minCol = 42585, "Not found", Sheets("Sheet2").Cells(thisRow, minCol + 1).Value)

 

[wideboydixon]

minCol is there to find the smallest column number that contains one of the items from Sheet1!A1:A8; you can leave it as 100. Normally, we find the last row of values (rather than hard-coding) so perhaps this works better:

Const sheet1Name = "Sheet1" ' Change as necessary
Const sheet2Name = "Sheet2" ' Change as necessary
Public Sub FindByRow()

Dim lastRow As Long
Dim thisRow As Long
Dim foundCol As Variant
Dim searchVal As Long
Dim minCol As Long

With Sheets(sheet2Name)
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    For thisRow = 2 To lastRow
        minCol = 100
        For searchVal = 1 To 8
            foundCol = Application.Match(Sheets(sheet1Name).Cells(searchVal, "A").Value, .Range("B" & thisRow & ":Z" & thisRow), 0)
            If Not IsError(foundCol) Then
                If foundCol < minCol Then minCol = foundCol
            End If
        Next searchVal
        If minCol = 100 Then
            .Cells(thisRow, "AA").Value = "Not found"
        Else
            .Cells(thisRow, "AA").Value = .Cells(thisRow, minCol + 1).Value
        End If
    Next thisRow
End With

End Sub

WBD