Excel 2010 VBA Macro Stops Running After Application.Wait

Hi All,

I am real new at VBA Macros. Tons of experience in the old Lotus 123 Macros but that was another day and time. My VBA Macro abiltiy is pretty much limited to those that I can record, or find answers for on the web. I am trying to write a macro that I can use to sum columns (diferrent 1's at different times) that have contiguously filled cells above, the number of rows of which may vary. After this section of the Macro is complete I want to continue on with more Macro steps that I have already written. The summations may take place in the same columns at different rows but the data will always be sepearted by blank cells. I am able to accomplish some of this with the following subroutine.

Sub SumCol()

Range("C6:D6").Cells.ClearContents 'This clears the summation formula from prior attempts.
Range("C3").Select 'This selects the top cell to be included in the sum.
Selection.End(xlDown).Select 'This selects C3:C5 (in this case) as the range to be summed.
ActiveCell.Offset(1, 0).Range("A1").Activate 'This makes the cell immediately below the data (C6) active for the sum formula..
Application.CommandBars.ExecuteMso ("AutoSum") 'This enters the formula =sum(C3:C5) in cell C6 (Which is what I want - but it doesn't close off the formula).
Application.SendKeys "{return}" 'The sum formula remained open and all processing stopped until I input this row and the next.
Application.Wait Now + TimeValue("00:00:10") 'Once this row was entered the sum formula closed & I got the right answer, but all further processing stops.

Range("D6").Select 'Without the row above this would become the active cell and =sum(C3:C5) would show up here at D6.
'With the Application.Wait row above all processing of this subroutine halts, never to restart.

ActiveCell.Value = 15 'The rest is just junk code to see if I could make this puppy continue working.
Application.SendKeys "{return}"
Application.Wait Now + TimeValue("0:00:10")

End Sub


SendKeys might be the problem as the Application.Wait doesn't slow the process down even for a second in this Macro. When I put the Application.Wait line in its own subroutine and run that by itself, it pauses Excel for whatever time I assign to it. I am a might bewildered over all this and would really appreciate your help. Thanks!

DMumme

AlphaFrog,

Thanks for the quick response. I will apply [CODE] tags in the future.

The problem I have with the response given is that it assumes the cells "C3:C5" as a given. The cells will be different each time the Macro is run. Different columns, different rows, different number of columns and rows. I have no problem identifying where I want the sum to be or the data I need summed. It doesn't fit the mold of being from the last used cell in a column to a given row above. It is always changing. That is why I was glad to find the "AutoSum" feature. Problem was getting the feature out of "edit" mode ie hitting return. I thought that might be what "SendKeys" could be used for. Any other ideas or am I just not getting it?

DMumme

Once again, thanks for your attention. This answer won't work because it assumes that the Macro knows the number of rows to be summed (in your example - 3). It does not. The answer may be 3 as in your example this time but next time it may be 50. I simply want to sum the numbers from [the cell above the active cell] to [the top cell above the active cell before a blank cell is encountered]. This will change all the time. It may be higher or lower in the same column as the last time the Macro was run. It may be in a different column than the last time the macro was run. It is very dynamic. It will not necessarily be all the rows in the column.

I added a reply weeks ago but I don't see it so I will try again.

romperstomper: This pretty much works. I edited your formula to be:

Please Login or Register  to view this content.

and this works like magic every time.
Thanks!
[SOLVED]