If values in cells are zero, how to increment the range formula by 1?

[kkaass]

Hi,

I am struggling with one of the formulae that I want to use.

I have a formula $H$2:$H$10, I want to change this formula every time I run a VBA such that it considers each additional cell in Column H, but that additional cell has a value zero.

So I run a VBA code for $H$2:$H$10, then I change this range to $H$2:$H$11 and re-run the VBA code, then the range changes to $H$2:$H$12, and the VBA code is run again. However, there are no values in cells H3 onwards.
So H2 has the value 1, but H3, H4 and so on have values 0 and cannot be changed due to the nature of the VBA code I am running.

I will be very grateful if someone can let me know how to change this range by adding one additional cell everytime the VBA code is run, when each additional cell has the value zero.

 

[AlphaFrog]

It's not clear if you want to change the range in the VBA code or in a worksheet formula?

Can you show your code?

 

[kkaass]

Thank you for your reply.

Actually I want to run a Solver model using VBA code. In this model, changing cells are $H$2:$H$10. So once I've set the Solver model in VBA, I will run it. However, when I want to run it again now I want to consider $H$2:$H$11 and then run the Solver VBA code again.
But I don't want to run all of this in one go, so I don't want to set a loop.

First the vba code (say Macro 1) will run $H$2:$H$10, then I have to run another VBA code (Macro 2) and then another (Macro 3). After that I want Macro 1 to run but now with $H$2:$H$11, then Macro 2 and then Macro 3. Then again I will run Macro 1 with $H$2:$H$12, then Macro 2 and Macro 3. So each time I want to run Macro 1, the only change in the Solver model is the changing cells ($H$2:$H$x+1).

So is it possible to set up changing cells such that rather than bychange:="$H$2:$H$10", its something like "$H$2:$H$10+i) where i=1,2,3,... however I don't know how many cells in column H will be filled so I cannot fix the number. The range has to stay open.

Or Range(H2:H) but in this case how will the macro know that now its time consider an additional cell in Column H because the cells are empty or is it necessary to set up some sort of reference?

 

[AlphaFrog]

This uses a Named Range to store the range between each macro use.
First run Reset_ChangeRange to establish or reset the named range to the start size.
Then run Increase_Range as often as you want.

[color=darkblue]Sub[/color] Reset_ChangeRange()
    Application.Names.Add "ChangeRange", "=" & Sheets("Sheet1").Range("H2:H10").Address(External:=True)
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Sub[/color] Increase_Range()
    
    Application.Goto "ChangeRange"
    MsgBox Range("ChangeRange").Address, , "ChangeRange Address"
    
    [color=green]'Increase +1 row[/color]
    [color=darkblue]With[/color] Range("ChangeRange")
        Application.Names.Add "ChangeRange", "=" & .Resize(.Count + 1).Address(External:=True)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
[color=darkblue]End[/color] Sub

 

[kkaass]

Thanks alot!
So should I use this code in place of bychange: "$H$2:$H$10" in Solver code?
So for vba code for solver, I should start off with the code for solver, and then when I need to enter bychange:= , how will I enter the code you provided in this changing cell formula?

 

[AlphaFrog]

If you don't show your code, I cannot tell you how to do it.

 

[kkaass]

Hi,
Sorry actually I have not used VBA for writing a code for Solver before, so I had to do some search before I can tell you the code.

So here's the code for Macro 1:
Sub Macro1()
solverok SetCell:="$o$3", MaxMinVal:=1, ByChange:="$H$2:$H$31"
solveradd CellRef:="$k$4:$k$5", Relation:=1, FormulaText:="$m$4:$m$5"
solveradd CellRef:="$k$6:$k$7", Relation:=3, FormulaText:="$m$6:$m$7"
SolverSolve UserFinish:=True
End Sub


The CellRef have already been specified before in the cells, their formulae are:
k4=sumproduct(index(F2:G31,0,1),H2:H31)
k5=sumproduct(index(F2:G31,0,2),H2:H31)
k6=sumproduct(index(D2:E31,0,1),H2:H31)
k7=sumproduct(index(D2:E31,0,2),H2:H31)
m4=index(F2:G31,N2,1)
m5=index(F2:G31,N2,2)
m6=o3*index(D2:E31,N2,1)
m7=index(D2:E31,N2,2)

O3 is the objective function and N2 is the number of unit.

So what I really want is that first this solver runs for the ranges H2:H31, D2:E31 and F2:G31.
Then I add values in the next row for columns D, E, F and G and now the above macro should run for ranges H2:H32, D2:E32 and F2:G32.
The again I add values in the next row for columns D, E, F and G and now the above macro should run for ranges H2:H33, D2:E33 and F2:G33.

and so on.

So whenever I add additional values in the following rows, the above macro considers all.
So can you please help me what should be formula in bychange:=, k4 to k7 and m4 to m7?

 

[kkaass]

Can someone please help me with the above issue? I will be very grateful as I cannot move forward unless this part is sorted out.