×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Automate Multiple Trials Results

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

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.



RE: Automate Multiple Trials Results

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.

RE: Automate Multiple Trials Results

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

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 Sub 

Should work!

Yakpol

RE: Automate Multiple Trials Results

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: Eng-Tips.com Forum Policies

RE: Automate Multiple Trials Results

(OP)
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.

RE: Automate Multiple Trials Results

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,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources