Automate Multiple Trials Results
Automate Multiple Trials Results
(OP)
I have an Excel file that contains two Work sheets (Calculation, Loads).In the Calculation work sheet there are six variables (Cells D13-D18) that change depending on which # trial is being checked.The results show up in cells G47 & G48 for each unique trial. There are a total of 104 trials with the different variables located in the Loads Work Sheet. Each trials variables start @ column E & Stop @ Column J. I copied Cells E3-J3 from the loads worksheet and did a paste special (transpose) to the calculation worksheet in cell D13. From there I copy cells G47-G48 from the calculation worksheet and did a paste special (transpose) back to the loads worksheet in Cells K3-L3. Is there a way to have excel go through this routine to fill in Coulumns K&L in the Loads Worksheet versus all the copy and pasting? I have attached the excel file for clarity.





RE: Automate Multiple Trials Results
I'd like to see the value on your report or Loads worksheet layed out the same on the Calculation worksheet so you can avoid transposing the result. Just do a Copy values for E68:L68 from the Calculation worksheet and paste it to the Loads worksheet.
This would save you from doing 2 separate operations of copying and transposing the results for the Px, Py, Pz, Mx, My and Tz and then picking up Tmin and Sr from G47 and G48 and then having to transpose them separately for each Trial.
In fact, I'd be tempted to put all the calculations on row 68 of the Calculation worksheet and replicate it down for all 104 trials. This will give you the entire Loads report at the bottom of your Calculation worksheet.
Hopefully you'll get some other ideas from others.
RE: Automate Multiple Trials Results
Cell D13 would be =VLOOKUP(B$12,Loads!(A$3:J$106,5). You could copy this down to the other cells, just changing the last # from 5 to 6, 7, 8, 9, and 10.
This wouldn't automatically fill up the data for the calculated tmin and SR, but at least it would automatically fill up your inputs.
RE: Automate Multiple Trials Results
Here's the way how to automate calculations with a macro. At each turn 6 loads are copied from worksheet 'Loads' to worksheet 'Calculation' and the results are copied back to worksheet 'Calculation'.
CODE
Sub Calculate_all_trials() Dim rngTrial As Range Dim rngLoads As Range Dim i As Integer Set rngTrial = Sheets("Loads").Range("E3:L3") Set rngLoads = Sheets("Calculation").Range("D13:D18") Do Until Len(rngTrial(1)) = 0 ' copy loads from sheet Loads to sheet Calculation For i = 1 To 6 rngLoads(i).Value = rngTrial(1, i).Value Next i ' copy results from Calculations to Loads rngTrial(1, 7).Value = Sheets("Calculation").Range("G47").Value rngTrial(1, 8).Value = Sheets("Calculation").Range("G48").Value ' move to the next line Set rngTrial = rngTrial.Offset(1) Loop End SubShould work!
Yakpol
RE: Automate Multiple Trials Results
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: Automate Multiple Trials Results
That is absolutely awesome thanks! Were you able to get the record macro to write that? I tried many different ways to have the macro record without any luck.
RE: Automate Multiple Trials Results
I wrote this macro, no recording. You may be able to record a macro to copy and paste data, but you still would have to wright a loop on your own.
Regards,