VBA code to feed inputs row by row into a Excel modeller and generate corresponding output

Hi All,

I am sure the solution to my query below is some sort of VBA code but I am a complete novice with VBA so I desperately need your help!

Here is a brief overview of my problem

(1) I have a workbook with 2 tabs - Input/Output and Modeller. Input/Output tab contains list of records (in thousands of rows and each record has 3 fields/inputs) that need to be run (doing some calculations) in the Modeller tab one row at a time to generate results which need to be fed back to Input/Output tab.

(2) How would I go about writing a VBA code to take each row of record (3 or more inputs per record), transfer inputs into modeller tab (where some calculations are done on the inputs), and then transfer the results (2 or more outputs per record) on same row as the corresponding inputs.

I think it may involve a For loop but that is as far as I know....

I have attached a small sample file to show you what I need help doing.

Any help will be awesome and much appreciated!

Thanks so much.

Yusuf

Hi Mick,

Thanks a tone for the quick response - code works! But here's the thing - the sample I attached was an over-simplification of actual modeller. In reality the formulae in modeller tab will be quite complex (referencing various other sheets) and I want to avoid writing all those in VBA.

If possible I would prefer that the Modeller do the calculations and I use VBA for inputting data and exporting results after each run in modeller. How should the code change to achieve this?

Thanks again.

Cheers,

Yusuf

No problem.
Replace the commandButton1.code with the below:-

Please Login or Register  to view this content.

Regards Mick

Hi Mick - Marvelous! Works perfectly.

You have saved me hours of work with your 2 examples of VBA code.

Thanks a million!

Cheers,

Y

Hi Mick,

Sorry one more question...

Does it matter whether I place the VBA code in specific sheet (i.e. InputOutput sheet) showing in project window or whether I insert a new Module in project window and add the code there.

What is best practice for VBA programming? Do we need to use modules?

Thanks,

Y

You can run the code wherever and however you like.
As you will see from the code , the two specific sheets are named , therefore, wherever you run the code, those are the sheets the code will act on .
I should use whatever suits you best. "CommandButton, Basic Module, shape" it's up to you .
Regrds Mick

Great. Thanks again.

You're welcome