Automate Multiple Trials Results 1

[eit09]

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.

 

[magoo2]

On your calculations sheet, I'd be tempted to duplicate the format of your Loads worksheet at the bottom of your Calculation worksheet (say in cells E68 to L68), where E68 = D13; F68 = D14; . . . J68 = D18, and K68 = G47; L68 = G48

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.

 

[zelgar]

One thing you can use to get the inputs from the Loads Sheet would be VLOOKUP. For example, if you put in Cell B12 the trial # (e.g., using data validation with a list with the source being =Loads!A3:A106 if manually looking for it or automatically in a macro loop)
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.

 

[yakpol]

eit09,

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'.

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 Sub

Should work!

Yakpol

 

[IRstuff]

Note that yakpol's solution can also be applied to Microsoft Word, i.e., one could copy/paste from Excel directly into Word, or vice-versa, if so desired

TTFN
faq731-376

 

[eit09]

yakpol,

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.

 

[yakpol]

eit09,

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,