VBA inquiry to return row number if match exists

[rn119]

I need the exact row number dynamic values (based on user input) might potentially exist in a macro. For example, if cell J2, K2, L2 values match table defined values in columns A, C and F (i.e., J2 has abc, K2 has 123, and L2 has xyz ...and it is only considered a match if those values all work in tandem in any row for columns A/C/F ). I can do this with an Index/IF function but I need the ability to throw a validation message to the user advising of the row number the user defined values exist in. Is there a way? Also if no match exists, then I need a validation message citing that Nothing exists.

 

[rn119]

Any update to this?

 

[NoSparks]

.... is only considered a match if those values all work in tandem in any row for columns A/C/F )

Can you elaborate as to what that means ?

Where on the sheet is the table located ?

Is there any possibility that more than only one row will match, if so, then what ?

 

[rn119]

So let's say Sheet 1 has the table and looks like this from Columns A1 to G6 in this example...

Item ID	Item Name	Contract ID	Contract Name	Contract Date	Loc ID	Loc Name
10980036	Stationery	837	RN TEST ABC	4/2/2014	503101	LOCATION ABC
10980037	Furniture	837	RN TEST ABC	7/1/2013	503102	LOCATION 123
10980038	Supplies	1334	RN TEST 123	7/1/2017	503103	LOCATION XYZ
10180052	Software	1334	RN TEST 123	9/1/2016	503104	LOCATION 456
10180044	Miscellaneous	1336	RN TEST XYZ	7/1/2015	503105	LOCATION 789

<tbody>
</tbody>

User defined values will fall in Sheet 1 in the following format (Columns A1 to C6 in this example).

Contract ID	Item ID	Loc ID
1339	10980036	503101
1339	10980037	503199
1455	10980038	503105
1500	10180052	503777
1336	10180044	503105

<tbody>
</tbody>

So in this example the user defined values (the last row here) match the table's last row based on Contract ID, Item ID and Location ID variables. So once the macro is invoked, it will tell the user of potential duplicates or if no duplicates throw a message that no duplicates were found.

In regards to your last inquiry, yes it is possible multiple rows can be a match. So then in that case, is it possible to have ALL row numbers called out in the validation message?

 

[NoSparks]

Assuming the J,K,L columns are that last table,
see if this is of any use, it presents an input box so you can just click the contract ID in column J that you want.

Sub MrE_rn119()
    
    Dim contractID As Range
    Dim rng As Range, fndID As Range
    Dim firstAddress As String, msg As String

    Set contractID = Application.InputBox("Select the Contract ID to look for" & vbLf & _
                     "by clicking a cell in column J ", "Find matching rows", Type:=8)
    
    If contractID.Count <> 1 Then Exit Sub
    If contractID.Column <> 10 Then Exit Sub
    
    With Sheets("Sheet1")
        Set rng = .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    End With
    
    With rng
        Set fndID = .Find(What:=contractID, LookIn:=xlValues, _
                         LookAt:=xlWhole, SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, MatchCase:=False)
                        
        If Not fndID Is Nothing Then
            firstAddress = fndID.Address
            Do
                If fndID.Offset(, -2).Value = contractID.Offset(, 1) And _
                   fndID.Offset(, 3).Value = contractID.Offset(, 2).Value Then
                    msg = msg & vbLf & fndID.Row
                End If
                Set fndID = .FindNext(fndID)
            Loop While Not fndID Is Nothing And fndID.Address <> firstAddress
        End If
    End With
    
    If Len(msg) = 0 Then
        MsgBox "No matching rows found for Contract ID " & contractID
    Else
        MsgBox "Contract ID match found on row(s) " & msg
    End If
   
End Sub