Prev: providing serial numbers with cells hidden
Next: PivotTable count unique
From: Valeria on 23 Apr 2010 06:21
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
From: Jacob Skaria on 23 Apr 2010 06:51
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
From: Valeria on 23 Apr 2010 08:17
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
 | 
Pages: 1
Prev: providing serial numbers with cells hidden
Next: PivotTable count unique