Dynamic Variable Cell range to reference in VBA Solver macro code

Question

=========================================

Sub RunSolver()

Sheets("Calcs").Select 'activate sheet "Calcs"

vr = "Calcs!$C$5:$Q$5" 'sets a variable called 'vr' to equal a equation that defines a the variable cells range

Application.Run "SolverReset"

Application.Run "SolverAdd", "$A$6", 2, "1"   'A6 is the sum of the variable cells and should equal 1

Application.Run "SolverAdd", "$A$28", 2, "$A$31"  'A28 is an equation based on the variable cells.  A31 is a user-entered 'desired value'

Application.Run "SolverOk", "$A$25", 2, "0", vr      'A25 is the target cell.  The goal is to minimize this and see if A28 can be achieved

Application.Run "SolverSolve", True

End Sub

=========================================

This code works just fine.  I have it running inside a pretty large project and module that pulls in real-time data, sorts it, analyzes it, and runs calculations off of it.  The challenge is this: the entire project begins with the user entering between 2 and 30 inputs.  Those inputs then dictate the size of many of my sheets, matrices, etc...  So the size of my variable cells range for my Solver will change with the number of user inputs.

Thus, in the above code, I need the "Q" in vr to be dynamic based on inputs.

ANY idea how to accomplish this?

Answer 1

Thank you, Ossie.  I tried running this code and it appears that nothing happens.  I'm not sure why that is.

Conceptually, I believe I see what you're trying to do.  We're just defining strLastCol as the address of the last nonblank cell in the fifth row.  Then we're defining vr as the range beginning with a known cell, C5, and end with the strLastCol.

Conceptually, this is exactly what I need and I thank you for thinking this through and simplifying it a few times.

But now when I run the macro, it processes 'something' (i.e. a blue circle cursor for about two seconds), but then nothing happens or changes.  If I open the Solver dialogue box, it is blank (appearing that nothing recently happened in there.)

Any thoughts?

• Drew

Here is my current code:

=====================================================

Sub Step10_SolverMin()

Sheets("Calcs").Select

 Dim vr

 Dim strLastCol As String

Sheets("Calcs").Select

With Worksheets("Calcs")

    strLastCol = .Cells(5, .Columns.Count).End(xlToLeft).Address

End With

vr = "Calcs!$C$5:$" & strLastCol

Application.Run "SolverReset"

Application.Run "SolverAdd", "$A$6", 2, "1"

Application.Run "SolverAdd", "$A$28", 2, "$A$31"

Application.Run "SolverOk", "$A$25", 2, "0", vr

Application.Run "SolverSolve", True

End Sub

======================================================

Answer 2

In your first version you have an error in the following line in your code. (Was correct in mine) 

vr = "Calcs!$C$5:$" & strLastCol 

strLastCol is an absolute address and contains the $ signs so should not have a $ sign after the colon.

Line should be as follows.

vr = "Calcs!$C$5:" & strLastCol

The first 2 versions of my code required the $ sign because I was only extracting the column Id.

I did not test with Solver because I did not have data.

I am off on vacation in a few hours and will not have a computer with me (or any computer access) so will not be able to reply again.

Answer 3

This is strange.  I tried running your second version that defines the array and then looks to the last column of the array.

Not only does it work, there is no solver dialogue box or alert.  (I'm sure I can fix that with some Application.Run's and then allow a more user-friendly error message if no solution is found.)

But, I thought the interesting part is that the below code works, but the 'third version' above does not.

==============================================================

Sub Step10b_SolverMin()

 Dim vr

 Dim varLastCol As Variant

 Dim strLastCol As String

Sheets("Calcs").Select

With Worksheets("Calcs")

    varLastCol = Split(.Cells(5, .Columns.Count).End(xlToLeft).EntireColumn.Address(0, 0), ":")

End With

strLastCol = varLastCol(LBound(varLastCol))

vr = "Calcs!$C$5:$" & strLastCol & "$5"

Application.Run "SolverReset"

Application.Run "SolverAdd", "$A$6", 2, "1"

Application.Run "SolverAdd", "$A$28", 2, "$A$31"

Application.Run "SolverOk", "$A$25", 2, "0", vr

Application.Run "SolverSolve", True

End Sub

==============================================================

Answer 4

Am I correct in assuming that the variable cells are on row 5 and you want to find the column Id of the last used column on Row 5 and use that in place of column Q when creating the variable vr ? If so, then the following.

Dim vr

Dim lngLastCol As Long

Dim strLastCol As String

Dim varLastCol As Variant

Sheets("Calcs").Select 'activate sheet "Calcs"

With Worksheets("Calcs")

    'Assign last used column in row 5 to a long variable

    lngLastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column

End With

'Assign the address of lngLastCol to a string

strLastCol = Columns(lngLastCol).Address(0, 0)

'Split the string at the colon and assign to a variable. (VarLastCol becomes an array)

varLastCol = Split(strLastCol, ":")

'Assign the first element of the array to strLastCol

strLastCol = varLastCol(LBound(varLastCol))

'Concatenate with strLastCol

vr = "Calcs!$C$5:$" & strLastCol & "$5" 'sets a variable called 'vr' to equal a equation that defines a the variable cells range

Additional info added to initial post.

I wrote the above code with the view to making it fairly easy to understand what occurs. You can combine some of the code above as per below.

Dim vr

Dim varLastCol As Variant

Dim strLastCol As String

Sheets("Calcs").Select 'activate sheet "Calcs"

With Worksheets("Calcs")

    'Split the address of last used column in row 5 and assign to variable (Array)

    varLastCol = Split(.Cells(5, .Columns.Count).End(xlToLeft).EntireColumn.Address(0, 0), ":")

End With

'Assign the first element of the array to strLastCol

strLastCol = varLastCol(LBound(varLastCol))

'Concatenate with strLastCol

vr = "Calcs!$C$5:$" & strLastCol & "$5" 'sets a variable called 'vr' to equal a equation that defines a the variable cells range

An easier method again I

Just get the full address of the last used cell on the row like the following

Dim vr

Dim strLastCol As String

Sheets("Calcs").Select 'activate sheet "Calcs"

With Worksheets("Calcs")

        strLastCol = .Cells(5, .Columns.Count).End(xlToLeft).Address

End With

'Concatenate with strLastCol

vr = "Calcs!$C$5:" & strLastCol 'sets a variable called 'vr' to equal a equation that defines a the variable cells range