Search used cells for values containing named range

[mp]

Hi all,
Is there an easy way to search all used cells in a workbook to detect
references to named ranges?
eg if I name a range "Labor2009" and a cell somewhere has a value like =
"Labor2009" how could i search that
something like pseudocode
For each oCell in oWorkBook.UsedCells(if such a collection existed)
If oCell.Value Like ("*target name*") then
'found reference
End if
Next
thanks
mark

 

[Patrick Molloy]

Option Explicit

Sub Macro1()
Dim ws As Worksheet
Dim found As Range
Dim addr As String
Dim col As Collection
Set col = New Collection
For Each ws In Worksheets
Set found = ws.Cells.Find("daily")
If Not found Is Nothing Then
addr = found.Address
Do
col.Add ws.Name & found.Address(False, False)
Set found = ws.Cells.FindNext(found)
Loop Until found.Address = addr

End If


Next

Dim index As Long
If col.Count > 0 Then
Set ws = Worksheets.Add
ws.Activate
For index = 1 To col.Count
ws.Cells(index, 1) = col(index)
Next
Else
MsgBox "no cells found"
End If


End Sub

 

[mp]

Option Explicit

Sub Macro1()
For Each ws In Worksheets
Set found = ws.Cells.Find("daily")
If Not found Is Nothing Then
addr = found.Address
Do
col.Add ws.Name & found.Address(False, False)
Set found = ws.Cells.FindNext(found)
Loop Until found.Address = addr

End If

thanks very much,
I'll look at the Find FindNext and Address properties to learn those (new to
me)
meanwhile i also remembered the .UsedRange property and found that
what I was looking for was .Formula, not .Value
I had changed some range names and wanted to update the formulas referring
to them
this worked for my very odd one time temp usage
Sub ReadFormulas()
Dim oRng As Range
Dim oWs As Worksheet
Dim oCell As Range
Dim oWb As Workbook

Set oWb = ThisWorkbook

For Each oWs In oWb.Worksheets
Set oRng = oWs.UsedRange

For Each oCell In oRng
If Len(oCell.Formula) > 0 Then

If InStr(1, oCell.Formula, "util", vbTextCompare) > 0 Then
Debug.Print "Old formula ", oCell.Formula

If Len(oCell.Formula) = 8 Then
Debug.Print "Old formula ", oCell.Formula
'=Utils98

If CLng(Right$(oCell.Formula, 2)) > 10 Then
oCell.Formula = "=Utils19" & Right$(oCell.Formula, 2)
Else
oCell.Formula = "=Utils20" & Right$(oCell.Formula, 2)
End If
Debug.Print "New formula", oCell.Formula
'=Utils1999
End If
End If
End If
Next oCell
Next oWs
End Sub

thanks
mark