PDA

View Full Version : Solved: 'find a range' problem



Remalay
08-22-2006, 03:13 AM
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. :banghead:

Can someone PLEASE show me where I am going wrong.


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


thnx
Remalay

Norie
08-22-2006, 03:23 AM
You can't use code like that to find multiple values within a range.

What exactly are you trying to do?

Remalay
08-22-2006, 03:30 AM
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
08-22-2006, 05:08 AM
maybe you can concatenate all in one cell the values of the cells of each row and look for that unique value

Remalay
08-22-2006, 05:17 AM
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
08-22-2006, 06:05 AM
A slight modification of the FindNext method should suffice

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

mdmackillop
08-22-2006, 06:10 AM
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
08-22-2006, 06:44 AM
:joy: Thanks mdmackillop, it works a treat.

mdmackillop
08-22-2006, 10:19 AM
Glad to help.:friends: