Excel2000: Searching a value in named range
 

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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Searching a value in named range
 

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)

"Arvi Laanemets" wrote in message
...
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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Searching a value in named range
 

Arvi,

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

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

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Arvi Laanemets" wrote:

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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Searching a value in named range
 

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" wrote in message
...
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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Excel2000: Searching a value in named range
 

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Arvi Laanemets" wrote in message
...
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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )