Create a routine to check the values of a row in a table and modify the ones of following row

Occasional Contributor

Hi, and thanks for coming here. I'm trying to create a routine that does  what the title says.

I have a table where each row represents an hour of the year, to which certain quantities (indicated on the columns) are associated every time-step (which corresponds to one row=1 hour). SOC is a quantity, to which i want to give  a value for every time-step, that must stay between certain limits (min-limit=0,15 and max-lim=0,95).


First, in the module "Variables_Constants" i declare all the public variables and constants that i must use in all the project. 

Then to perform the requested operation i created the routine "TEST_check_prev_SOC", which sees if the value of SOC of the previous cell risked to reach one of these limits, and if in case it does it gives you a certain feedback for the current possible value of SOC. This part works fine. The problem comes when I call "TEST_check_prev_SOC" in the main routine, "TEST_SOC_logic". This routine has the purpose to automatise the procedure for each time-step, applying "TEST_check_prev_SOC" to the i-th step, seeing the corresponding limits and then doing the same on next row. Doing this it should create an array in which it saves the values of SOC corresponding to the various rows (and possibly i would like to make this array multidimensional, saving also the values of the other columns and of the obtained limits. But this will come in the future). At the moment the system does what it must do only for the first step, then the dynamic array expands as it should its dimensions but it stores always only the same value, without going on the next row with the procedure. 

How can i solve this? Thanks a lot for your help, and sorry for the long explanation . 

5 Replies
best response confirmed by Aless1275 (Occasional Contributor)


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

@Hans Vogelaar 

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,


Set focus_SOC = focus_SOC.Offset(1, 0)


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?


Sometimes it helps to add


Application.Cursor = xlWait


at the beginning, and


Application.Cursor = xlDefault


at the end, but you'll have to  test it.