Solved: 'find a range' problem

[Remalay]

Relative newbie ....
Striving to code the finding of a range within a range. The return always seems to be the first match to 'F1', ignoring the rest of the range.

Can someone PLEASE show me where I am going wrong.

[VBA]
Set c = Range("A1:C30").Find(Range("F1:H1").Value, LookIn:=xlValues, LookAt:=xlWhole)
Range("G12").Value = c.Row
[/VBA]

thnx
Remalay

[Norie]

You can't use code like that to find multiple values within a range.

What exactly are you trying to do?

[Remalay]

OK. Thanks for that - someone has mis-informed me.... ah well.
I'm trying to find a unique matching row of data (colums A thru F) from a range of 2,000+ records, in order to retrieve the associated data from adjacent columns.

[ALe]

maybe you can concatenate all in one cell the values of the cells of each row and look for that unique value

[Remalay]

Thanks for that ALe, an option I might be forced to employ if a 'neater' solution is not forthcoming (I find it hard to believe there isn't one).

rgds
remalay

[mdmackillop]

A slight modification of the FindNext method should suffice
[VBA]
Sub FindGroup()
Dim ToFind As Range, Found As Range, c As Range
Dim FirstAddress As String
Set ToFind = Range("E3:E5")
With Worksheets(1).Range("a1:a500")
Set c = .Find(ToFind(1), LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If c.Offset(1) = ToFind(2) And c.Offset(2) = ToFind(3) Then
Set Found = Range(c, c.Offset(2))
GoTo Exits
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Exits:
MsgBox Found.Address
End Sub
[/VBA]

[mdmackillop]

Just noticed you're working with rows. Just switch around the c.offset to column offsets and you should be OK. Only search Column A for the initial value, not Columns A-C as your first code.

[Remalay]

Thanks mdmackillop, it works a treat.

[mdmackillop]

Glad to help.