Thank you! I think it's close. It seems to be getting stuck in a loop within
the function when I step through it. I also get a Run time error 1004:
Application-defined or object-defined error when I run it, and it is
highlighting the Else (ActiveWorkbook.Names.Add Name:=v, RefersTo:=s1 &
r.Offset(0, 1).Address) when I debug. Any ideas?

"Gary''s Student" wrote:

Give this a try:


Function NameExist(s As String) As Boolean
Dim n As Name
NameExist = False
If ActiveWorkbook.Names.Count = 0 Then Exit Function
For Each n In ActiveWorkbook.Names
If s = n.Name Then
NameExist = True
Exit Function
End If
Next
End Function


Sub nameLister()
Dim r As Range
Dim v As String
Dim s1 As String
s1 = "=" & ActiveSheet.Name & "!"
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
Set r = Cells(i, 1)
v = r.Value
If NameExist(v) Then
s = ActiveWorkbook.Names(v).RefersTo & ","
ActiveWorkbook.Names.Add Name:=v, RefersTo:=s & r.Offset(0, 1).Address
Else
ActiveWorkbook.Names.Add Name:=v, RefersTo:=s1 & r.Offset(0,
1).Address
End If
Next
End Sub
--
Gary''s Student - gsnu200826


"JZ" wrote:

I have data as follows in A1:B10.

ACCMLS $31,416.00
ACCPRO $35,430.00
ACCSUP $25,834.00
ACCSUP $27,167.47
ACCSUP $18,969.39
ACCSUP $30,281.00
ACCSUP $25,234.00
ACCSUP $26,114.00
ACCSUP $27,296.00
ACCSUP $25,234.00

I need a macro that names each range of numbers with the name in column A
just to the left of the first instance. For example, B1 should be named
ACCMLS. B2 should be named ACCPRO, and B3:B10 should be named ACCSUP.

Can anyone help?