Defining a range of cells

Reggie

New Member
Joined
Mar 15, 2002
Messages
10
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Thanks again Dave, but the same error pops up. I cut and paste so there shouldnt be any retyping errors.

Any ideas?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top