Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search used cells for values containing named range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search used cells for values containing named range
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search used cells for values containing named range
"Patrick Molloy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search a named range | Excel Worksheet Functions | |||
search values in named formulas | Excel Programming | |||
Macro to select only cells containing values within named range | Excel Programming | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Search cells in a column and return yes/no if they are present in named range | Excel Programming |