Processing a list of data through a separate excel setup

[curry2445]

I have a complicated spreadsheet which only allows one input case at a time. I have around 200 input cases. Instead of creating 200 spreadsheets I would like to write a marco which runs each case through the spreadsheet and saves the output in a summary sheet. 

In my simplified example below I would want to copy A2 to F2 and B2 to F3 then copy F4 to C2 as a number value, then repeat copying A3 to F2 and so on until the C column is complete. 

I am new to VBA. I feel I need to use a loop but I'm not sure how to write it to achieve the above. 

Any advice would be appreciated. 

Thanks

[#2]

@curry2445 I know you said it is a simplified example but I'm not sure why you need to copy the values over to perform that calculation and then copy the value back.  Why can't column C just be =a2*b2 and fill down?  Even if for some reason you do need to copy the values then you still only need to do it once.

  If you truly need to do all these steps and want/need VBA then you probably want to do most if not all the calculations in VBA instead of sheet formulas then.  So although I could show you a simple VBA that copies cell contents from A2 to ... it is really not worth much since it is almost certainly not what you should do.  

  So maybe give us a more accurate example (ideally the actual spreadsheet with personal info like names and other personal info anonymized).

[#3]

@mtarler 

I need to input cells C13, C14, C21, C22, D26, F26 and G26 into the MAIN tab in the attached sheet from an input sheet which looks like the capture image.

E2=C13, D2=C14, F2=C21, F2=C22, G2=D26, J2=F26 and K2=G26

Then I want to extract from the spreadsheet tab 'Cases' Q15 and V15 and insert them N2 and O2 respectively. 

Then continue this on repeat from 2 to ~200.

[#4]

@curry2445  So it is as I feared, lol.  If this is something you will do a lot in the future you may want to invest in tracing each formula back and creating the correct functions accordingly.  That said, in the attached I wrote a macro to loop through all rows in an active sheet and do as you asked but be careful because I did NOT add any error checking and such (i.e. it will overwrite without prompting and such)

I tried to make the names of variable as easy for you to understand to tweak as needed.  Instead of hardcoding the name of the sheet, I opted for using cell A1 to point to a cell in the other workbook on the main sheet and pulled the workbook and worksheet info that way.

I noted that you wanted to fill cells N2 and O2 even though it appears L2 and M2 are the "open" cells.

I also noted that after running it the values for your sample were "not good".

I hope this template will at least let you figure out how to complete it.

Best of luck.

[#5]

@mtarler 

Thanks for this. It's really appreciated. 

I am trying to run it and I running into error. Also definetely at my ability as a user rather than your code.  

I have updated cell A1 to have the location where the calculation spreadsheet is saved on my computer. I have saved both sheets in this folder.

When I run the macro it gets stuck at:

Set MPAmain = Application.Range(Mid(Asheet.Range("a1").Formula, 2)).Worksheet

Do I need to link the sheets? I am currently running the macro out of sheet 1.

My mistake in my earlier capture I missed off )2 but that what the location I wanted than formula - thanks. 

Thanks again.

[#6]

@curry2445 I'm sorry I didn't explain it well.  

I changed the macro and sheet a little so I think it makes more sense for you.

Now the macro sheet only has 2 cells; one for the address of the data sheet and one for the address of that formula worksheet.  The macro assumes the data sheet is the only or at least the first sheet in the workbook.  If not you can tweak the macro (or the workbook).

If this works I hope you will like and mark as best answer :) 

[#7]

Hi @mtarler,

This is working. Thanks a lot you've saved me a lot of time. 

I just have one final question, if you don't mind. I attempted it myself, but its completely wrong. 

I want to add a column to my summary sheet with bar sizes in it and ask the macro to look up the bar size in the Cases tab B13:B18 then return the corresponding Mres V13:V18 in the original summary sheet O2...

See attached:

Capture - my weak attempt.
Capture1 - the additional column
Capture2 - the information I'm talking about. 

[#8 · Accepted solution]

@curry2445  i think you want to look up bar size based on column P and then copy the corresponding M' and Mres values to columns n and o.  At least that is how I updated the macro.

[#9]

@mtarler Thanks for all your help. This is perfect.