Excel geniuses?

[Silly Car]

Is it possible to set up excel to automatically populate fields on a macro based worksheet, collect the output, then enter another row of data to the fields and repeat until the data runs out?

I am attempting to find the optimum results from two sets of data and the macro based worksheet runs multiple calcs to get the output I’m after so it would be a ball-ache recreating those calcs over multiple lines of data and the worksheet is password protected so I can’t C&P the formulae into a new document to speed up the process.

[Mussels]

So one workbook controlling another?
This looks like a good starting point but it's not something I've ever had the need to do.
https://stackoverflow.com/questions/349 ... -workbooks

[Rockburner]

Where's Olly when you need him?

[Mr. Dazzle]

This is 100% possible, I had a workbook that did exactly this at my old job....

...which i didn't create. Was done by a graduate nerd using vba.

Sorry.

[A_morti]

What you're describing isn't a macro, but a power query.

Power query is a very powerful way to collect and collate data. If you're willing to get into the rabbit hole, you'll soon be importing the data to a data model rather than a sheet so it will handle over the excel limit of 1m rows, and do calculations in a flash.

[A_morti]

Also: unless the password is an honesty-bix thing and you'll be in trouble for removing it... You can remove it.

Save the file as compatible with excel 97, then Google search VBA password breakers. Mostly they will tie up your computer for ages and not work, unless you know to save the file in compatibility mode first.

[Mussels]

Power query won't feed parameters into Excel, trigger a macro, collect the results and move on to the next parameter.

Power query is great at some things but it's rubbish at iterative processes and probably needs coding with R or Python to do this, an adaptable VBA method would be much simpler.

[Silly Car]

Thanks all, the opportunity has passed as I got a minion (small yellow chap with one eye, squeaky voice) to crunch the numbers multiple times to get as close as possible to the desired outcome. Then the clients decided they just wanted to split the investment 50:50 as I originally suggested although this was after we found an optimum solution *

* excel workbook calculated the tax payable, based on an individual’s circumstances, on surrender of a particular type of investment.

[A_morti]

Power query won't feed parameters into Excel, trigger a macro, collect the results and move on to the next parameter.

Power query is great at some things but it's rubbish at iterative processes and probably needs coding with R or Python to do this, an adaptable VBA method would be much simpler.

I'd have to see the inputs and outputs, but probably it could swallow the whole process in a handful of conditional columns without going to the trouble of macros. Maybe.