Forum Discussion
Create a routine to check the values of a row in a table and modify the ones of following row
In TEST_check_prev_SOC, the line
Set mySh = Application.ThisWorkbook.Worksheets("Sheet1")
is superfluous - it has already been set in the calling macro TEST_SOC_logic.
And the line
Set focus_SOC = mySh.Range("D2")
resets focus_SOC to D2 each time after it has been changed in the calling macro.
So remove those two lines from TEST_check_prev_SOC
In TEST_check_prev_SOC, the line
Set mySh = Application.ThisWorkbook.Worksheets("Sheet1")
is superfluous - it has already been set in the calling macro TEST_SOC_logic.
And the line
Set focus_SOC = mySh.Range("D2")
resets focus_SOC to D2 each time after it has been changed in the calling macro.
So remove those two lines from TEST_check_prev_SOC
thanks a lot for your answer! Yeah, i totally missed that "focus" that was resetting the procedure everytime. Now i modified the code to make it work, giving to the choice of the current SOC a small logic behind it, seeing also what's the value of prev_SOC, to simulate what i will have to implement next.
But now there is another problem that i don't know how to fix. If i compile the program in debug mode with f8, or putting a "Stop" (like in line 54) everything works as wanted. But if I take it out and compile it normally, excel crashes, probably entering in an infinite loop, so that i have to close the whole program. There must be something wrong in how i set the " Do Until focus_DATE="" " at the beginning, cause i wanted the cycle to function until there are no more time-steps indicated in the column B. But apparently he continues indefinitely. Do you know what should i fix for this?
You don't update focus_DATE in the loop, so it remains cell B3 and never becomes empty,
Below
Set focus_SOC = focus_SOC.Offset(1, 0)
insert
Set focus_DATE = focus_DATE.Offset(1, 0)
The code will then finish at row 8761.
- okay perfect, thank you!
Now is working all fine, but for the all year it's taking quite a while to perform the simulation. To speed it up i could put at the beginning of the code (just after the variables initialisation):
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
and then just before the end of the main sub:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Is there something more that i can do to speed it up?
