Macro to define ranges
 

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?

Macro to define ranges
 

JZ,

Yes, you could do that, but it is not needed. Better would be to use subtotals, a pivot table, data
filters, or SUMIF, COUNTIF, or SUMPRODUCT formulas.

HTH,
Bernie
MS Excel MVP


"JZ" wrote in message
...
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?

Macro to define ranges
 

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?

Macro to define ranges
 

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?

Macro to define ranges
 

I am not sure, but we can avoid the RefersTo logic if all the names in column
A are blocked together (sorted rather than random):

Sub RangeRover()
'gsnuxx
Range("B1").Name = Range("A1").Value
Dim n As Long, i As Long
n = Cells(Rows.Count, "A").End(xlUp).Row
Dim r As Range
Set r = Range("B1")
For i = 2 To n
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
Set r = Union(r, Cells(i, 2))
Else
Set r = Cells(i, 2)
End If
r.Name = Cells(i, 1).Value
Next
End Sub

Try this one out.
--
Gary''s Student - gsnu200826


"JZ" wrote:

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?