Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redefining a Named Range In A Loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redefining a Named Range In A Loop
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 -- Don Guillett SalesAid Software "Frank" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redefining a Named Range In A Loop
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("A1:D20") '<<====== 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 '<<============== --- Regards, Norman "Frank" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Redefining a Named Range In A Loop
Perfect Norman !
That's exactly what I was looking for. Frank "Norman Jones" wrote: 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("A1:D20") '<<====== 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 '<<============== --- Regards, Norman "Frank" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If any cell in named range = 8 then shade named range | Excel Programming | |||
Loop thru named range of second sheet | Excel Programming | |||
plz help: creating named range in VBA, loop goes haywire | Excel Programming | |||
Redefining a new Range area - Thanks Nick Hodge and Norman | Excel Programming | |||
Redefining a new Range area | Excel Programming |