Redefining a Named Range In A Loop

[Guest]

I want to loop through a range of cells and test for a condition. When the
first cell that meets the condition is found, add a defined name (i.e.
MyRange). As I continue looping through the cells, each cell that meets the
condition has to be added to that defined name.

Is there an easier way to do this rather than manipulate the RefersTo string?

Frank

 

[Don Guillett]

will this work?

Sub setupname()
With Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row)
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mystring = mystring & "," & c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
'MsgBox Right(mystring, Len(mystring) - 1)
ActiveWorkbook.Names.Add Name:="newname", RefersTo:= _
"=" & Right(mystring, Len(mystring) - 1)
End With
End Sub

 

[Norman Jones]

Hi Frank,

Is there an easier way to do this rather than manipulate
the RefersTo string?

Try, perhaps, something like:
'==============>>
Public Sub ATester3()
Dim Rng As Range
Dim rCell As Range
Dim Rng2 As Range
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<====== CHANGE
Set SH = WB.Sheets("Sheet1") '<<====== CHANGE
Set Rng = SH.Range("A120") '<<====== CHANGE

For Each rCell In Rng.Cells
If rCell.Value > 10 Then '<<====== CHANGE
If Rng2 Is Nothing Then
Set Rng2 = rCell
Else
Set Rng2 = Union(rCell, Rng2)
End If
End If
Next rCell

If Not Rng2 Is Nothing Then
Rng2.Name = "Test2"
End If

End Sub
'<<==============

 

[Guest]

Perfect Norman !

That's exactly what I was looking for.

Frank