PDA

View Full Version : need a 2 input solver



mattrix
07-25-2016, 09:39 PM
Hi all,
I need a 2 input 'solver', in EXCEL 2007.
What I have is a worksheet affected by 2 parameters (cells). I also have a result cell that tells me how good the combination of parameters is. I need to, roughly, optimize the result by changing the 2 parameters.

I thought to set up a table with values for parameter-1 in the first row and values for parameter-2 in the first column. Then to select the table and run a VB macro that copies these values into the worksheet and then copies the result from the worksheet into my table.

ie,

set tbl = current range
for each value in column 1 of tbl
for each value in row 1 of tbl


copy values to worksheet
execute worksheet
copy result to cell(row, column)




Trouble is, it has been so long since I used the EXCEL object model that I can't remember the syntax to refer to all the cells properly, etc. If someone could give me something to start from it would be greatly appreciated.

mattrix
07-26-2016, 07:51 AM
OK. I've sorted the syntax.
Here it is for any one who comes across this thread in a search.
Its not a very smart routine, but I only had to do it once.

Sub Macro1()
' Keyboard Shortcut: Ctrl+a
Dim tbl As Range
Dim RRow, CCol As Integer
Set tbl = Selection

For RRow = 2 To tbl.Rows.Count


ActiveSheet.Range("G4").Value = tbl.Cells(RRow, 1).Value
For CCol = 2 To tbl.Columns.Count


ActiveSheet.Range("H3").Value = tbl.Cells(1, CCol).Value
ActiveSheet.Calculate
tbl.Cells(RRow, CCol).Value = ActiveSheet.Range("J4").Value
Next

Next


End Sub