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

Answer 1

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