Macro to keep runing total problem

[CVinje]

I have a macro, written by somebody else, that is designed to take a numeric value from an assigned cell, add it to the existing value in another cell and place the new total in that second cell. I needed this as a solution to the hours of overtime in a week being erased each time a new week started, but had to have a way to track the year's total. Here is an example of the code:

Please Login or Register  to view this content.

Cell J8 is the first cell with any data in it to be added, column V (specifically cell V8) is where the yearly running total is kept. The above code works perfectly if the first cell is a number or empty; however, it gives an error - mismatch - if there is any text (other than a number) in the first cell, and only the first cell. Subsequent cells in the column with text do not give an error. Any help on how to modify this code to accept input other than a number in the first cell without giving the error?

Thanks for any help,

CVinje

[StephenR]

Don't see why it should error like that, but you could a stipulation that column J is a number before doing the sum:

Please Login or Register  to view this content.

[CVinje]

Thanks for the reply; however, the modified code results in a different error - this time with no regard to content of cells. The error is Run-time '1004', Application defined or object defined. I tried reading the help to understand how I could correct this, but it seems it's a general error and I don't know how to fix it.

Thanks again for any help,

CVinje

[Leith Ross]

Hello CVinje,

All numbers on the worksheet are stored as Double data type. Since each cell is Variant data type, you can check to see what data type it contains.

Please Login or Register  to view this content.

[CVinje]

Thanks for the continued advice; however, when I use the suggested changes in the code, I still get a type mismatch error. Also, note that I had to change the code you posted by closing the parenthesis after the 5 - I don't know for sure if this was correct. Again, the mismatch only occurs if there is something other than a number in the first cell. I tried changing the variable from 5 to others, but to no avail.

CVinje

[Leith Ross]

Hello CVinje,

Go catch on the missing parenthesis. Provided you are running this code on Excel 2003, which is what my system is running, it does work. Evidently, you have a problem elsewhere. You should post your workbook so all the code can be reviewed and the problem resolved.

Corrected Code

Please Login or Register  to view this content.

[CVinje]

I discovered the problem, and I should have caught it before my previous post - the first modification you gave was as follows:

Please Login or Register  to view this content.

I did catch the parenthesis that was needed, but missed the call in the VarType line for Cells(j, "V") when I think you meant (j, "J") - as is posted in your second code.

It's working now, still have to run the sheet through some paces, but I'm pretty sure we're good to go!

Thanks <A TON> for the help!!

CVinje