Get row number that matches a set of values of range in VBA

user_18887939 0
Viewed 73

I am struggling to find the row number from a list of values that matches a set of values of a range from different worksheets.

Range value in 'Sheet2' to match with the same range value in 'Sheet1' with its associated row number

Sub findmyrow()

   Dim myrange As Range
   Dim myrow As Long
   Dim mysheet As Worksheet

   Set myrange = ThisWorkbook.Sheets("Sheet2").Range("A1:Z1")
   myrow = ThisWorkbook.Sheets("Sheet1").Range("A1:Z1000").Find(What:=myrange, LookIn:=xlValues).ROW

   Debug.Print myrow

End Sub

The code only matches the value of the first column of 'Sheet2' = A and not 'A B' against the result found in the list of 'Sheet1'.

vba excel
2 Answers

I don't think that find can do that job as it only takes one value to find.

Maybe one of the formula-masters here on Stackoverflow have a better solution.

With VBA you can do it this way:

Option Explicit

Public Sub test_getRow()

Dim rgFind As Range, rgLookup As Range

With ThisWorkbook
    Set rgFind = .Worksheets("Sheet2").Range("A1:Z1")
    Set rgLookup = .Worksheets("Sheet1").Range("A1:Z1000")
End With
    
Debug.Print getRow(rgFind, rgLookup)
    
End Sub


'This is the generic routine to find the row
Public Function getRow(rgFind As Range, rgLookup As Range) As Long

'First check the input ranges
If rgFind.Columns.Count > rgLookup.Columns.Count Then
    MsgBox "Number of columns of rgFind have to be less or equal to the number of columns of rgLookup.", vbExclamation
    Exit Function
    
ElseIf rgFind.Rows.Count > 1 Then
    MsgBox "rgFind has to be a single row.", vbExclamation
    Exit Function
End If

'use arrays of values to increase performance
Dim arrFind As Variant: arrFind = rgFind.Value
Dim arrLookup As Variant: arrLookup = rgLookup.Value

Dim rowL As Long
Dim colF As Long
Dim rowMatch As Long


For rowL = 1 To UBound(arrLookup, 1)
    'checking each row of the lookup range
    For colF = 1 To UBound(arrFind, 2)
        'now compare each value of the lookup row to each value of the find row
        If arrFind(1, colF) = arrLookup(rowL, colF) Then
            'if equal store current row
            rowMatch = rowL
        Else
            'if not reset rowMatch and exit for-loop as this row can't match anylonger
            rowMatch = 0
            Exit For
        End If
    Next
    If rowMatch > 0 Then Exit For   'we found the row
Next

getRow = rowMatch

End Function
user_165784240

Match a Row

Sub MatchRow()

    Dim lrg As Range: Set lrg = ThisWorkbook.Worksheets("Sheet2").Range("A1:Z1")
    Dim lData As Variant: lData = lrg.Value
    
    Dim srg As Range
    Set srg = ThisWorkbook.Worksheets("Sheet1").Range("A1:Z1000")
    Dim rCount As Long: rCount = srg.Rows.Count
    Dim cCount As Long: cCount = srg.Columns.Count
    Dim sData As Variant: sData = srg.Value
    
    Dim MyRow As Long, r As Long, c As Long
    
    For r = 1 To rCount
        If sData(r, 1) = lData(1, 1) Then
            For c = 2 To cCount
                If sData(r, c) <> lData(1, c) Then Exit For
            Next c
            If c > cCount Then MyRow = r: Exit For
        End If
    Next r
    
    Debug.Print MyRow
    
End Sub
user_98140690
Related