ExcelBanter - How to have another worksheet do the calculations

Seems like a reasonable approach to me:

You'll have to change this to use the right cells on the calculation sheet:

Option Explicit
Sub testme()

Dim InputWks As Worksheet
Dim CalcWks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set InputWks = Worksheets("sheet1")
Set CalcWks = Worksheets("sheet2")

With InputWks
'headers in row 1
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With CalcWks
For Each myCell In myRng.Cells
'populate the CalcWks with values from the input sheet
.Range("a1").Value = myCell.Value
.Range("x99").value = mycell.offset(0,1).value
.range("iv323").value = mycell.offset(0,2).value

'do the calculation
Application.Calculate

'take some values back from the calcwks to the input sheet
myCell.Offset(0, 3).Value = .Range("b1").Value
myCell.Offset(0, 4).Value = .Range("c1").Value
myCell.Offset(0, 5).Value = .Range("d1").Value
Next myCell
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

I have a worksheet that calcultes rates interactively. I now want to run it
in a batch mode using another worksheet. This requires the new worksheet to
send an array to my existing worksheet wait for it to calculate it and then
copy the results back into another array from my existing workseet to my new
worksheet.

Can this done using macros? If so, what is the recommended solution?

Thank you in advance.
Kouros

--

Dave Peterson