Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Numbering (2002)

    Jonathan,

    The macro in your workbook puts a fixed value in column A whenever a cell in column B changes. This does not seem a good idea to me:
    <UL><LI>If you modify a name, there is no a priori need to change the number in column A, so the macro executes far too often.
    <LI>As you found out, it wreaks havoc when you re-order the table.[/list]As an alternative, you can create a macro in a standard module to insert a row and put the formula =ROW()-3 in the first cell of the new empty row:

    Sub InsertRow()
    ActiveCell.EntireRow.Insert
    Cells(ActiveCell.Row, 1).Formula = "=ROW()-3"
    End Sub

    In the attached workbook, I have removed the original macro, created a standard module with the above macro, and made it easily available by putting a command button on the worksheet.

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

    Custom Numbering (2002)

    Hello:

    Actually, this continues a previous thread of mine, "Add Numbering" from March 2003, but the links in the "reply to your post" e-mails I received no longer work.

    To recap, someone wrote a macro to add numbers to column A as I insert data in a given row. I am attaching the spreadsheet.

    The problem now is that when I sort (Data | Sort), the numbers in Column A remain with their corresponding data. By contrast, what should happen when I sort (say by ListServ)--and what I'm hoping someone here can do for me by modifying the macro and uploading the spreadsheet back to me via the lounge--is that while columns B - E should change, column A should not change.

    By the way, is this macro even necessary; is it possible to make Excel start numbering the rows at row 4, so that row 4 is row 1?

    Thanks so much!

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

    Re: Custom Numbering (2002)

    Jonathan,

    You have modified your post, but you haven't reacted to my reply to the original version. If this is because my reply wasn't helpful (which is entirely possible) it would have been better if you had posted a reply explaining what didn't work. If you have changed the original question, the current situation is confusing, since my reply may not be relevant to the question as it stands now.

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

    Re: Custom Numbering (2002)

    Hi Hans,

    Sorry it took me so long to get back to you; I almost forgot! BTW, I only modified my original post since I told the lounge to "watch" this thread. Anyway, thank you very much for your patience.

    There's just one problem with your macro: I dislike the Insert command button; I would rather simply enter text in column B.

    So, let me ask you this, from my original post: Is a macro even necessary? Is it possible to change the row headings so that they start at row 4, i.e., what is now row heading 4 becomes 1?

    Really, all I want to do is to know -- at a moment's notice, i.e., without subtracting 3 for the first 3 rows -- how many members are in the club? Isn't what I'm trying to do common?

    If not, any other ideas to eliminate the Insert command button?

    Thanks so much, Hans! I certainly appreciate your help!

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

    Re: Custom Numbering (2002)

    1. You can't modify the row headers, they are "hard-coded" into Excel.

    2. By changing the macro in your attachment to

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, ActiveSheet.Range("B1:B65536")) Is Nothing Then
    Target.EntireRow.Columns(1).Formula = "=ROW()-3"
    End If
    End Sub

    it will work as intended, I think.

    3. If you just want to count the number of list entries, and if there is nothing below the list, you can put the following formula in a cell, for example in cell D1:

    =COUNTA(B4:B65536)&" members"

    See attached modified version.

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

    Re: Custom Numbering (2002)

    Hi Hans,

    Wow--this is great! Thanks so much!

    Just 2 last things; I tried changing the macro myself, but have no idea what I'm doing [img]/forums/images/smilies/smile.gif[/img].

    1. The second spreadsheet you uploaded contains 21 members; the original one I uploaded contained 75...

    2. Would you put D1 in A2, and insert a blank row after row 2?

    Thanks again, Hans!

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

    Re: Custom Numbering (2002)

    Jon,

    Q1: In the spreadsheet I attached the second time, I had inserted and deleted rows to see if the macro behaved as intended.
    Q2: Done, see new attachment (with the original 75 members)

    The macro works as follows (note that is has been changed slightly because of the insertion of a new row.)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, ActiveSheet.Range("B5:B65536")) Is Nothing Then
    Intersect(Target, ActiveSheet.Range("B5:B65536")).EntireRow.Columns( 1).Formula = "=ROW()-4"
    End If
    End Sub

    The Worksheet_Change event occurs each time a cell on the worksheet is changed by the user or by an external link. The Target argument is a range that refers to the changed cell or cells.
    We want to act if the user has changed something in column B, in particular in cell B4 or below. So we test whether the target range and the range B5:B65536 have cells in common (have a non-empty intersection). If so, we take the entire rows of this intersection, and than the cells of these rows in the first column (column A). The formulas of these cells are set to =ROW()-4, so they display the row number-4 (it was row number-3 in the original version, but since we inserted a row, we must subtract 4 instead of 3.)

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

    Re: Custom Numbering (2002)

    Hans,

    Everything's great; thank a lot!

    If I may be so rude as to ask for one last thing -- this is my last request -- would you please put in A3 a running total (just like the club members) of the ListServ members -- this means any Y in column E -- and insert a blank row after row 3?

    Again, I am very indebted to you.

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

    Re: Custom Numbering (2002)

    Here you go.

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

    THANK YOU!

    Anything you ever need--just ask! Thanks Hans!

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

    Add Running Totals

    Hi again Hans,

    I'd like to add running totals to another spreadsheet (attached) for columns G, H, I, J, K, and L.

    Since you did this with my last spreadsheet, would you please tell me how to do for this one? I'd actually like to add the running totals myself [img]/forums/images/smilies/smile.gif[/img].

    Thanks!

  12. #12
    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 Running Totals

    If you want them in col A as in the previous example just do something like:
    =COUNT(E:E) &" Merchandise Costs"
    =COUNT(F:F) &" S&H Costs"

    etc

    If you want them in the particular column, you can NOT use this, since it will be a circular reference.
    You could uses something like:

    =COUNT(E4:E65536) &" Merchandise Costs"
    =COUNT(F4:F65536) &" S&H Costs"

    Steve

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

    Re: Add Running Totals

    Where would you like the running totals to be inserted?

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

    Re: Add Running Totals

    With one blank row above the running-totals row, I'd like the running totals always at the bottom of columns I, J, K, L, and M; whenever I insert a row, the running-totals row should likewise shift down one row. Thanks, Hans!

  15. #15
    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 Running Totals

    I find the best way in cases like this is to leave the 2 blank rows and then add the formula. In the row right above the formula highlight it and color it and make it narrower. Then link the formula to that cell. SO when you INSERT above the line the formula ranges always expand.

    for example, In I14 enter:
    <pre>=SUM(I3:I13)</pre>

    and copy it to j14:M14

    If you insert lines below the header row (3) or above row 13 (the row above the subtotal) the sum will always expand. Then I would color the bkgrd of row13 gray and make it narrower. Then I know to insert ABOVE this row.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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