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.
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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.