Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Defining a range of cells

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Reggie, stand-alone.



  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Any ideas?

  8. #8
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •