Excel2000: Searching a value in named range

[Arvi Laanemets]

Hi

In a workbook I have a named range Weeks, which contains string values in
format "yyyy.ww". What is the best way to find the position of string value
XWeek (which equals p.e. to "2006.01") in range Weeks using VBA?

I can get the range definition
ThisWorkbook.Names("Weeks")
, but I haven't find a way to search in it.
Of-course I can read the range into VBA as an array, add a couple of array
search functions I have written before for some other project, and search
this array, but maybe there is an easier way.


Thanks in advance

 

[Bob Phillips]

Arvi,

Have you tried adding the sheet and using Find

ThisWorkbook.ActiveSheet.Range("Weeks").Find(XWeek).Select


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

Arvi,

Range("Weeks").Find(What:="2006.01")

Returns a cell that you can use Row/Column properties to return location
details.

Regards,

Chris.

 

[Rick Hansen]

Arvi, Here is some code that I believe will get you started...

Good Luck, Rick


Option Explicit
Sub SearchFor()
Dim searchRng as Range, Frng as Range

Set searchRng = Range("Weeks")

Set Frng = searchRng.Find(what:="2006.01", LookIn:=xlValues,
lookat:=xlWhole)
If Not Frng Is Nothing Then
msgbox("2006.01 found at " & Frng.Address)
else
msgbox("2006.01 not found")
End If

End Sub

 

[Arvi Laanemets]

Hi

I decided for
Application.WorksheetFunction.Match(EndWeek,
ThisWorkbook.Sheets("Weeks").Range("Weeks"), 0)

Thanks anyway - following your tips I found out, that I have always to
determine sheet name, when referring to range on it.