Bulk Load - Multiple Input

[Suil]

Hi Folks

See attached.

Want to create a VBA which will take individual rows, input into Bulk Load tab and produce result. Clear input and input next row and produce result. Number of bulk load items varies so VBA loop needs to vary.

Any assistance greatly appreciated.

Suil

[jomili]

Suil,
Nothing was attached.

[Suil]

Suil,
Nothing was attached.

Attached now, thanks.

[jomili]

Suil,
I'm confused about the goal. You have the Bulk Load page, the Single Input page, and the Calculator. On Bulk you have a value labled "Required Output" which comes from the Calculator, which is populated by Single Input. So if we paste the second line of values from Bulk to Single, the "Required Output" changes from 120 to 134. Each line will change the required output, but I don't see that you're keeping that output anywhere, so what are you trying to accomplish here?

Also, since Row 1 is on Bulk Load page when we start, should we clear Row 1 and shift up, or leave Row 1 blank, copy Row 2 to "Single Input", clear Row 2 then move to Row 3, etc?

[Suil]

Thanks, this is very much a simplified example. In reality the calculator is very complex, embedded with multiple if statements. I cannot share it here as it is
for work and is as a result confidential.

At the moment the tool allows input one scenario at the time, I would like to develop a bulk load facility.

[jomili]

I understand. See if this gets it for you. On your example the data started on Row 3, hence the 3 beginning i value. Not sure of your VBA expertise, but if you need a button to start it and don't know how let me know.

Please Login or Register  to view this content.

[Suil]

thank you so much for this.

Would it be possible to keep the data in the bulk load tab and populate the cells in column H and also keep this output. So the content in
Single Input would clear each time the Macro is run.

thanks again.

Suil

[jomili]

Sure. How's this?

Please Login or Register  to view this content.

[jomili]

BTW, you DO know you could get rid of everything but the Bulk Load page, right? Simply replicate your Calculator formula down column H of your Bunk Load sheet, no VBA required.

[Suil]

Again, thank you so much for all your assistance on this - it is very much appreciated.

One other question, in the real world there will be very sensitive data behind the calculator. Is there any way of hiding this data and to be 100% sure that the
data cannot be accessed. I have tried hiding tabs and password protecting them but tabs can be very easily unhidden and I downloaded a VBA script online to
"hack" a password very quickly.

Any suggestions on how this might work?

[jomili]

Nothing is fool proof, but there's a few things you can do:
1) For Sheets, there's Visible, Hidden, and VeryHidden settings. Visible and Hidden properties are accessible in Excel proper, but VeryHidden can only be unhidden through VBA code or the VBA Editor
2) Your calculator is fed by your Single Input tab, which pulls from your Bulk Load tab. The Bulk Load tab can be in a different workbook, OR both it and the Single Input tab can be in a different workbook. Then you can feed the Calculator with either a VLookup or a direct link to the cells.