Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    708
    Thanks
    48
    Thanked 1 Time in 1 Post

    Add numbering (2002)

    How do I add numbers, in order, to column A?

    I cannot use the row headings because the rows I need numbered begin with row 4.

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add numbering (2002)

    If you want numbering to start with 1 in cell A4, enter the formula =Row()-3 in cell A4, and fill down as far as needed.

  3. #3
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    708
    Thanks
    48
    Thanked 1 Time in 1 Post

    Re: Add numbering (2002)

    Thanks, Hans! That works.

    But, how do I make it so that new rows automatically come with a number in column A?

    Thanks again.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Add numbering (2002)

    If you add this code in the worksheet object in VB, it will automatically add the value (the row# - 3) to column A when something is added into column B.

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, ActiveSheet.Range("B1:B65536")) Is Nothing Then
    Target.Offset(0, -1).Value = Target.Row - 3
    End If
    End Sub
    </pre>


    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    708
    Thanks
    48
    Thanked 1 Time in 1 Post

    Re: Add numbering (2002)

    Thanks, Steve!

    While in the worksheet, I went to Tools | Macros | Macro, typed in "Numbering," and clicked create. I then pasted your text, removed the duplicate "End Sub" and closed the window.

    But it didn't work. How exactly should I add the macro?

    Thanks again.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Add numbering (2002)

    It needs to go in a worksheet object:
    Go to VB Editor (ALt-F11)
    Make sure "project Explorer" is visible (ctrl-R)
    (It is usually on the left side near the top, it is a "tree diagram" similar to what you would see windows explorer)
    Find the "tree" for your file"
    -VBAProject(Filename)
    under the branch
    - Microsoft Excel Objects
    You will see a listing of all the sheets in the workbook.
    Dbl-click on the worksheet that you want this macro to run in.
    Copy the attached code and paste it into the "code window" pane in the upper right of the VB Editor
    Close VB Editor (alt-Q)
    Enter /change items in column B and watch the magic.

    Steve

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Add numbering (2002)

    Hi Jonathon,

    If you go back into the VBA Editor, you should see the "Project Explorer" panel, probably on the upper left side of the screen. If it's not visible, click on View|Project Explorer. In the Project Explorer panel, you should see the name of your workbookand and, under it, its sheets and your code module.

    What you need to do is to cut the macro from the code module and paste it into whichever sheet mdoule you want the macro to act on.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    708
    Thanks
    48
    Thanked 1 Time in 1 Post

    Re: Add numbering (2002)

    Hi Steve,

    Thanks again for your response.

    Alas, the text in your previous post (#233197)--i.e., the macro--won't display...

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Add numbering (2002)

    What do you mean "won't display"?
    Here it is again, with no formattiing

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, ActiveSheet.Range("B1:B65536")) Is Nothing Then
    Target.Offset(0, -1).Value = Target.Row - 3
    End If
    End Sub

  10. #10
    5 Star Lounger
    Join Date
    Jun 2001
    Location
    USA
    Posts
    708
    Thanks
    48
    Thanked 1 Time in 1 Post

    Thanks

    Thanks; that worked!

Posting Permissions

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