Hawaii-Geek Posted July 19, 2010 Report Share Posted July 19, 2010 How can I stop a Repeat at a =Sum cell in Excel? Win7 / Macro Express PRO Most of my macros in excel need to End Repeat at a =Sum cell (when it hits a cell with a =SUM in the formula .. Total) Is there a way to do this? Thank you, HG Quote Link to comment Share on other sites More sharing options...
Cory Posted July 19, 2010 Report Share Posted July 19, 2010 The only way I could see to do this is to capture the formula bar contents in each iteration and test If Contains "=sum". Using the Control features might be handy for this. Also you might consider looking to the first blank cell and then back up one. Usually there's a blank cell after the sum and if that's the case it would be a lot easier to check for that. When doing things like this I often copy the entire column to the clipboard, copy that to a variable, then Split that variable into an array. Then I do whatever I need with it and then past the results back. It seems like more work but usually it's less and it runs way faster and more reliably. Quote Link to comment Share on other sites More sharing options...
Hawaii-Geek Posted July 19, 2010 Author Report Share Posted July 19, 2010 The only way I could see to do this is to capture the formula bar contents in each iteration and test If Contains "=sum". Using the Control features might be handy for this. Also you might consider looking to the first blank cell and then back up one. Usually there's a blank cell after the sum and if that's the case it would be a lot easier to check for that. When doing things like this I often copy the entire column to the clipboard, copy that to a variable, then Split that variable into an array. Then I do whatever I need with it and then past the results back. It seems like more work but usually it's less and it runs way faster and more reliably. Thank you Cory, Not sure if I fully understand how I will actully convert what you suggest to a Express Macro I kinda get that I could capture the text in the formula bar ... and then test for a IF contains =sum but, not sure how to do that in Express Macro. sorry. On the blanks ... on a row of numbers ... there will be blanks and maybe zeros ... before a =sum is hit (at the end). * doing this with a IF "=sum" END repeat ... would be the most elegant Stop. Right now I am doing it with a ... countA ... to get how many numbers are in the row ... and then having Express macro do the window pop up to ask the number of repeats ... which is not as automated. or as fast. Thank you, HG Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.