Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to have another worksheet do the calculations
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Calculations not working | Excel Worksheet Functions | |||
Worksheet calculations | New Users to Excel | |||
Not running calculations until I leave worksheet | Excel Discussion (Misc queries) | |||
Inserting worksheet calculations into macros | Excel Worksheet Functions | |||
How can I format a worksheet to carry out all calculations to 2 d. | Excel Discussion (Misc queries) |