Iterate through Names for text
I would replace a few lines to correct possible flaws:
For Each zNm In ActiveSheet.Names ' Iterate through each element.
Set zNmRngB = Nothing ' ***ADDED***
On Error Resume Next
Set zNmRngB = range(zNm.name) ' ***MODIFIED***
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = zNmRngB.Find(what:="IMP", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False) ' ***MODIFIED***
.......... rest of your code is unchanged, I didn't look at your "formating
code" .....
--
Regards,
Luc.
"Festina Lente"
" wrote:
Luc, I only want to look at local names, in this case. Here is my code
as is, working or so it seems :)
Dim zNm As Name
Dim zNmRng As Range
Dim zNmRngA As Range
Dim zNmRngB As Range
For Each zNm In ActiveSheet.Names ' Iterate through each
element.
On Error Resume Next
Set zNmRngB = zNm.RefersToRange
'zNmRngBAdd = zNmRngB.Address
On Error GoTo 0
If Not zNmRngB Is Nothing Then
Set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not zNmRngA Is Nothing Then
'Conditional formating code
Range(zNmRngA).Select
Selection.FormatConditions.Delete
'LCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=" &
Selection.End(xlDown).Offset(12, 0).Address
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
End With
Selection.FormatConditions(1).Interior.ColorIndex = 22
'UCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" &
Selection.End(xlDown).Offset(11, 0).Address
Selection.FormatConditions(2).Interior.ColorIndex = 19
'end Conditional formating code
End If
End If
Next
PapaDos wrote:
It may actually look in LESS names than you intended.
"Sheet.names" only returns the "local" names, like "Sheet2!my_range" and not
the names define at the workbook level like "my_range".
To define those names in Excel's user interface you have to enter the
sheetname at the begining of the NAME...
By the way, Tom's code has a few flaws... worst than mine ! ;-]
If you are basing your code on his, post your code, if you want help in
correcting its behaviour...
--
Regards,
Luc.
"Festina Lente"
" wrote:
Luc, If I use ActiveSheet.Names
dosent that limit the search to just the names on the active sheet? or
does it still look at a lot of unecessary space?
Thanks for bringing this up
Robert
PapaDos wrote:
It doesn't make much sense to search ( with find() ) the entire active sheet
instead of your named ranges. You will get "false positives" on many
occasions...
--
Regards,
Luc.
"Festina Lente"
" wrote:
Thanks Tom, I Have got that working. Im using Activesheet because
there are many sheets from on template that have the same names and I
may need to run diffent pass/fail criteria on differnt sheets.
Thanks Again
Tom Ogilvy wrote:
Dim zNmRng as Name
Dim zNmRngA as Range
Dim sNmRngB as Range
For Each zNmRng In ActiveSheet.Names ' Iterate through names.
On Error Resume Next
set zNmRngB = zNmRng.RefersToRange
On Error goto 0
if not zNmRngB is Nothing then
set zNmRngA = Cells.Find(what:="IMP", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext,
MatchCase:= False, _
SearchFormat:=False)
if not zNmRngA is nothing Then
'Conditional formating code goes here
End If
end if
Next
You also might Try
ThisWorkbook.Names instead of Activesheet.Names
It depends on which names you want to look at.
--
Regards,
Tom Ogilvy
" wrote:
I need to search through the Named Ranges in a sheet and for the ones
that contain certain text, in this case "IMP" then I need to set a
conditonal format to the range. I have the conditonal formating part
working but Im having trouble iterating through the names for the text
My code is
Dim zNmRng
For Each zNmRng In ActiveSheet.Names ' Iterate through names.
If zNmRng = Cells.Find(what:="IMP", After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False) Then
'Conditional formating code goes here
End If
Next
I am obviously using the find method incorrectly
I also want to have a second condition if, the first conditon is true
then I need to see if the name has "ID" in the name and if so skip
the code. Im sure that once I figure out how to do the first part, the
second is just a mater of nesting.
Here are some examples of the named ranges:
IMP_100_Hz ---- do
LC_100_Hz ------ Skip
IMP_ID ------------ Skip
IMP_20K_Hz ---- do
Any help will be appreciated
Robert
|