Defining a range of cells

[Reggie]

I was hoping somebody could help me with the following.

I need to define a name automatically using a macro similar to the one below, except I need it to name a range of cells not just an individual cell.

So where the following macro names the cell (one row below) when any input is found in row A between 30 & 3000, I need it to name the range of cells between each input in A.

So if I have YY typed in A40 and YY typed in A60 I want the range A41:A59 to be named "Name1" etc..

Hopefully it will not take too much adjustment to the macro below, but unfortunately my macro ability leaves plenty to be desired so any help will be greatly appreciated.

Thanks.


Sub AddingNames()
i = 1
Application.ScreenUpdating = False
Range("a30:a3000").Select
For Each cell In Selection
If ActiveCell <> "" Then
ActiveWorkbook.Names.Add Name:="Name" & i, RefersToR1C1:=ActiveCell.Offset(1, 0)
i = i + 1
End If
ActiveCell.Offset(1, 0).Select
Next cell
Range("a1").Select
Application.ScreenUpdating = True
End Sub

[Dave Hawley]

Hi Reggie


This may not be 'exactly' what you want, but I think it should help somewhat.

Sub AddingNames()

Dim i As Integer

For i = 2 To Range("a30:a3000").Rows.Count - 1
If Not IsEmpty(rCell) Then rCell(i, 0).Name = "Name " & i
Next


End Sub

[Reggie]

Thanks for that Dave.

I'm not quite sure where to plug that in the existing macro, or whether it is a standalone macro in itself.

Sorry mate, macro skills are a little lacking.

[Dave Hawley]

Reggie, stand-alone.

[Reggie]

Thanks again Dave

No luck. It gives me a runtime error:

Run-time Error '1004':
Method 'Range' of object'_Global' failed

Tried placing parts of the original macro in it aswell, but wth no success.

It is frustrating because it only requires a slight change on what already works, but unfortunately that appears to be the hard part.

Thanks again for your effort.

[Dave Hawley]

Oops, sorry Reggie, This should work

Sub AddingNames()

Dim i As Integer

For i = 2 To Range("a30:a3000").Rows.Count - 1
Range("A30:A3000").Cells(i, 1).Select
If Not IsEmpty(Range("A30:A3000").Cells(i, 1).Value) Then _
Range("A30:A3000").Cells(i, 1).Name = "Name" & i
Next


End Sub

[Reggie]

Thanks again Dave, but the same error pops up. I cut and paste so there shouldnt be any retyping errors.

Any ideas?

[Dave Hawley]

Reggie, You must be doing something wrong! It works as expected here. The Select bit was only in there for de-bugging purposes.

This is what it does, it loops through the range A31:A2999 and names each cell in sequencial order if the cell in NOT empty. If you are still having problems just drop me an email and I'll send you the Working Exmaple

Sub AddingNames()

Dim i As Integer

For i = 2 To Range("a30:a3000").Rows.Count - 1

If Not IsEmpty(Range("A30:A3000").Cells(i, 1).Value) Then _
Range("A30:A3000").Cells(i, 1).Name = "Name" & i
Next


End Sub