none
VBA for Excel COUNTA function

    Question

  • Hello,

    I need to keep a running total in Excel. However, the COUTNA function will not work, because I am using a from that keeps adding new records to the top of the worksheet. So therefore when I run my application the funchtion range (B4:B30) changes to B5, then B6, and so on, after each new record. I aslo tried to making the B4 absolute, $B$4, to no avail. Anyone have any suggestions for my problem?

    PS - I also have tried using a macro that changes the destination cell for te runnign total to change the range back to the original B4:B30 in an effort to curb this problem. Once again, it did not work properly.

    Here is a code snippet for my form to insert a new row at the top of the worksheet.
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    Selection.Font.ColorIndex = 1

    Any Suggestions? Thanks in advance!

    Unbroken73
    Thursday, May 17, 2007 1:21 PM

All replies

  • Hi Unbroken73,

     

    If you insert the row in between B4 and B30, you should be ok. Meaning don't insert a new row at B4. Instead insert it at B5. If you must have the newer row at the top use the copy function. I think this code will work but I haven't tested it, and please note I'm familar with a slightly different syntax?

     

    Insert at top row example:

    'make room for a new value

    Rows("4:5").Select

    Selection.Insert (xlShiftDown)

     

    'copy the top value (we're moving it down 1 row)

    Rows("4:4").Select

    Selection.Copy (Rows("4:5"))

     

    'insert the new value

    Selection = "hope this works!"

     

    Johnny

     

    Thursday, May 17, 2007 6:42 PM