Can someone help me? I made this and for some reason it does not go to the 2nd row, unless I am in Step Into. But run by itself, it just continues without stop.
I am hoping that when you read it, you will easily see where I went wrong. I appreciate your advice in advance!
Please Login or Register to view this content.
Which loop... the commented out one or one of the other ones? If one of the others, which one?
The colmun loop. I think that is the one that gets hung up. But could be wrong.Originally Posted by Rick Rothstein
Is that the one you commented out?
@Rick: guessing it's this ...
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
The column loop is not commented out. Sorry, I did not understand.
The problem is that it does not go to the next rownum. So I was thinking that the loop TMS quoted it not finishing correctly. I was hoping that an expert eye could see the mistake, as I cannot after spending 2 days Debugging.
Try row 208 in the attached .pdf spelling of variable 'colnum' is spelled wrongly as 'colmun'
Thank you for pointing that out. Okay, I fixed that. But I don't believe thats my only problem.
Okay, it appears to be working now. So maybe it was the misspelling or another small changes made. Thank you guys for your guidance!
Looks pretty likely given the context of the blockSo maybe it was the misspelling ...
Please Login or Register to view this content.
Use Option Explicit at the top of every module to avoid misspelling variable names.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
I have one more question about this VBA. Is there a way to speed it up? I ran it overnight last night (6.5 hrs) and it calculated 188 rows. I have over 100,000 rows per workbook. That would be 2.9 million hours to complete one workbook....and I have 22 workbooks todo.
Specifically, my question pertains to advice regarding:
* writing the code to be a lot more efficient
* Is there another method to calculate this quicker
* like Access Database
* another computation software
* etc.
One of my computers has 32 cores, 128 GB of ram. Plus I ran it on a second computer at the same time. It only has 4 cores and 16 GB ram. It computed at the same pace. As they say, VBA is not multi-threading, so I eliminated the computer hardware as not a solution.
Last edited by stearno; 09-28-2020 at 11:14 PM.
You have the advantage of us ... you have the data, the code, and you know what you are trying to do with it.
In general terms
1. Switch off screen updating
2. Set Calculation mode to Manual
3. Switch off Event Handling
4. Avoid selecting cell/rows/columns/worksheets etc
5. Avoid full column references in formulae, especially Array Formulae and SUMPRODUCT
6. Check Conditional Formatting ... might be worth clearing it and reinstating it
7. Some people would advocate reading the entire sheet into an array, processing it, and dropping the result back
8. It may be something Power Query can handle but that's not my area of expertise
Understood. Thank you for the advice! Will give it a try.
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks