a faster, more efficient way for this math loop

[dad812]

I have this in worksheet _change , and seek assistance in making this code more efficient / faster. It calculates a balance from the previous sheet and places it in "J" of the current sheet. Thanks in advance

Please Login or Register  to view this content.

[mgs73]

Is there a reason you are using VBA for this, and not formulas?

Looking at the code, I don't see what could be taking any significant amount of time - how long does it take?

[dad812]

I have not timed it, wanted another set of eyes on it, I would like the cell's(J) value when at 0 to allow another Beginning value, it is a running balance. I had used a formula, but that means the cell must be locked, then unlocked ... etc., so the easiest way for me to overcome was do the math in VBA. Cell stays unlocked & when balance hits 0 I am able to start a new total.

This is only part of the code in the worksheet_change event, I can post all of it if you want to have a look.

[scottiex]

yes post the whole code.
or better yet - post a sheet with any sensitive information removed.
Also if you can time it on your system and tell us what "slow" is. For me this seems fast - but maybe your other code is causing a lot of looping or something?

[dad812]

This is all the code I have in the sheets, Worksheet_Activate() & Worksheet_Change, I need the cells to update based on cell value change - in order to get desired results I need to run in both events. And are you asking for the workbook with only a couple of sheets in it ? it may not be until tomorrow. thanks for looking

Please Login or Register  to view this content.

[mgs73]

Please Login or Register  to view this content.

That is an interesting way to get the row - hope you never have more than a 100

This may be what you were looking for:

Please Login or Register  to view this content.

Do you need to Enable Events where you do? If you put it along with the Application.ScreenUpdating = True, that should speed things up.

[dad812]

I'll make those suggestions happen (mgs73) and check out the results. Had an issue early on in doing the math after mouse click - it was the current row vs target row, wasn't going to let it win.

would you still like me to upload a copy of the spreadsheet ? (scottiex) and thx...