Assigning New Name For Each Cell That Matches Criteria

C

CalumMurdo Kennedy

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
 
B

Bob Phillips

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)
 
B

BrianB

'------------------------------------------
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
'------------------------------------------------
 
P

Patrick Molloy

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
Click to expand...
If cell.Value > 0 Then
i = i + 1
Cell.Name = "Name" & i
End If
Next cell
 
Top