Dear experts,
I need to compare 2 ranges of data by a concatenation of cells (for both). I
have the following code

For i = 1 To 10
Set f = sha.Range("a" & i & ":d" & i).Find(what:=shb.Range("a1:d1"),
lookat:=xlWhole, LookIn:=xlValues)
If Not f Is Nothing Then MsgBox f.Address
Next i

But it does not do what I want as it just stops the comparison at the first
cell value, it does not concatenate the values of the range.

Could you please help me?

Many thanks in advance.
Kind regards

--
Valeria

The below macro will search for Sheet2 Range("A1:D1") in
Sheet1.Range("A1:D10") and if found displays the row number in Sheet1...

Sub Macro()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim varFound As Variant, varSearch As Variant
Dim strAddress As String, blnFound As Boolean, cell As Range


Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set varSearch = ws2.Range("A1:D1")

With ws1.Range("A1:A10")
Set varFound = .Find(varSearch(1), LookIn:=xlValues)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
blnFound = True
For Each cell In varSearch
If cell.Value < ws1.Cells(varFound.Row, cell.Column) Then
blnFound = False: Exit For
End If
Next
If blnFound = True Then MsgBox varFound.Row: Exit Sub
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address < strAddress
End If
End With

End Sub


--
Jacob (MVP - Excel)


"Valeria" wrote:

Dear experts,
I need to compare 2 ranges of data by a concatenation of cells (for both). I
have the following code

For i = 1 To 10
Set f = sha.Range("a" & i & ":d" & i).Find(what:=shb.Range("a1:d1"),
lookat:=xlWhole, LookIn:=xlValues)
If Not f Is Nothing Then MsgBox f.Address
Next i

But it does not do what I want as it just stops the comparison at the first
cell value, it does not concatenate the values of the range.

Could you please help me?

Many thanks in advance.
Kind regards

--
Valeria

it works beautifully, thank you very much!
--
Valeria


"Jacob Skaria" wrote:

The below macro will search for Sheet2 Range("A1:D1") in
Sheet1.Range("A1:D10") and if found displays the row number in Sheet1...

Sub Macro()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim varFound As Variant, varSearch As Variant
Dim strAddress As String, blnFound As Boolean, cell As Range


Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set varSearch = ws2.Range("A1:D1")

With ws1.Range("A1:A10")
Set varFound = .Find(varSearch(1), LookIn:=xlValues)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
blnFound = True
For Each cell In varSearch
If cell.Value < ws1.Cells(varFound.Row, cell.Column) Then
blnFound = False: Exit For
End If
Next
If blnFound = True Then MsgBox varFound.Row: Exit Sub
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address < strAddress
End If
End With

End Sub


--
Jacob (MVP - Excel)


"Valeria" wrote:

Dear experts,
I need to compare 2 ranges of data by a concatenation of cells (for both). I
have the following code

For i = 1 To 10
Set f = sha.Range("a" & i & ":d" & i).Find(what:=shb.Range("a1:d1"),
lookat:=xlWhole, LookIn:=xlValues)
If Not f Is Nothing Then MsgBox f.Address
Next i

But it does not do what I want as it just stops the comparison at the first
cell value, it does not concatenate the values of the range.

Could you please help me?

Many thanks in advance.
Kind regards

--
Valeria