Assigning New Name For Each Cell That Matches Criteria
 

Hi All,

I'm trying to build a macro that will check all the values in a range
to test which cells have a value greater than zero. Then for each
cell that does to name its address (in order) as Name+1 then Name+2
etc so that I can use Name+1 to return the address of that cell later
in another Macro.

I can check each cell without problem using For Each in Range and I
can test using If Then, however it is the nameing part that I can't
get, any help is as always greatly appreciated.

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk

Assigning New Name For Each Cell That Matches Criteria
 

For Each cell In Selection
If cell.Value 0 Then
i = i + 1
ActiveWorkbook.Names.Add Name:="Name" & i, _
RefersTo:="=" & cell.Address
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CalumMurdo Kennedy" wrote in message
om...
Hi All,

I'm trying to build a macro that will check all the values in a range
to test which cells have a value greater than zero. Then for each
cell that does to name its address (in order) as Name+1 then Name+2
etc so that I can use Name+1 to return the address of that cell later
in another Macro.

I can check each cell without problem using For Each in Range and I
can test using If Then, however it is the nameing part that I can't
get, any help is as always greatly appreciated.

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk

Assigning New Name For Each Cell That Matches Criteria
 

'------------------------------------------
Sub test()
Dim MyRange As Range
Dim Counter As Integer
'--------------------
Set MyRange = ActiveSheet.Range("A1:A50")
Counter = 1
For Each c In MyRange.Cells
If c.Value 0 Then
ActiveSheet.Names.Add _
Name:="Name" & Counter, _
RefersTo:="=" & c.Address
Counter = Counter + 1
End If
Next
End Sub
'------------------------------------------------

--
Message posted from http://www.ExcelForum.com

Assigning New Name For Each Cell That Matches Criteria
 

or





-----Original Message-----
For Each cell In Selection
If cell.Value 0 Then
i = i + 1
ActiveWorkbook.Names.Add Name:="Name" & i, _
RefersTo:="=" &
cell.Address
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
For Each cell In Selection
If cell.Value 0 Then
i = i + 1
Cell.Name = "Name" & i
End If
Next cell
"CalumMurdo Kennedy" wrote in
message
. com...
Hi All,

I'm trying to build a macro that will check all the
values in a range
to test which cells have a value greater than zero.
Then for each
cell that does to name its address (in order) as
Name+1 then Name+2
etc so that I can use Name+1 to return the address of
that cell later
in another Macro.

I can check each cell without problem using For Each
in Range and I
can test using If Then, however it is the nameing part
that I can't
get, any help is as always greatly appreciated.

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk


.

Assigning New Name For Each Cell That Matches Criteria
 

Thanks everyone for the very quick responses!

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk

-----Original Message-----
'------------------------------------------
Sub test()
Dim MyRange As Range
Dim Counter As Integer
'--------------------
Set MyRange = ActiveSheet.Range("A1:A50")
Counter = 1
For Each c In MyRange.Cells
If c.Value 0 Then
ActiveSheet.Names.Add _
Name:="Name" & Counter, _
RefersTo:="=" & c.Address
Counter = Counter + 1
End If
Next
End Sub
'-------------------------------------------------


---
Message posted from http://www.ExcelForum.com/

.