Forum Discussion
Processing a list of data through a separate excel setup
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
8 Replies
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).
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.
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.
