hi all,
How do you progammaticly refer to named ranges.
I am trying to write a macro that loops through named
ranges to find items. I have the macro mostly written and
can loop through sheets but i want it to loop through 4
named ranges only because it may find the items outside
the ranges which i don't want. It does return the range
address but not the sheet name.
can someone point me in the right direction.
Zoomer
progress so far..........
Sub macfind()
Dim sStr As String
Dim sh As Worksheet
Dim rng As Range
Dim Item1 As Range
Dim Item2 As Range
sStr = InputBox("Enter item to search for")

For Each sh In ThisWorkbook.Worksheets
If sStr < "" Then
Set rng = Nothing
Set rng = sh.Range("A1:IV65536").Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
If Not rng Is Nothing Then
Sheets("sheet1").Activate
Range("AA1").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = rng.Address
End If
Next sh
If rng Is Nothing Then
MsgBox sStr & " was Not found"
End If

End Sub

Range("MyName").Find


--
Regards,
Tom Ogilvy

"Zoomer" wrote in message
...
hi all,
How do you progammaticly refer to named ranges.
I am trying to write a macro that loops through named
ranges to find items. I have the macro mostly written and
can loop through sheets but i want it to loop through 4
named ranges only because it may find the items outside
the ranges which i don't want. It does return the range
address but not the sheet name.
can someone point me in the right direction.
Zoomer
progress so far..........
Sub macfind()
Dim sStr As String
Dim sh As Worksheet
Dim rng As Range
Dim Item1 As Range
Dim Item2 As Range
sStr = InputBox("Enter item to search for")

For Each sh In ThisWorkbook.Worksheets
If sStr < "" Then
Set rng = Nothing
Set rng = sh.Range("A1:IV65536").Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
If Not rng Is Nothing Then
Sheets("sheet1").Activate
Range("AA1").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = rng.Address
End If
Next sh
If rng Is Nothing Then
MsgBox sStr & " was Not found"
End If

End Sub