Run a data series through custom calculator??

[Myer]

I created a calculator (calculates volume of gas vented to atm based on geometry-which is constant and pressure-variable) and I’d like to be able to enter the pressure at which the gas was vented in a column on another page and in the adjacent cell I’d like to get the volume that the calculator calculates. The problem I’m running into is if I reference the cells back and forth as I enter the new pressure values the calculator calculates a new volume, therefore over writing previous volumes. How can I enter the pressure, have it run through the series of calculations on the ‘calculation’ page and return the associated volume for many inputs?

[JBeaucaire]

That would require a macro.

Post up your workbook with clear instructions on where the values are and how to use your calculator and we can suggest something specific.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[Myer]

On the first sheet, columns X and Y are where I want to get the results from the calculator, which is titled blow down volume calculator. The values in columns V and W on the sheet Gas Loss Report are the inputs for the calculator and the values I want to get back are in cells M26 and L27 on the calculator sheet.

[shg]

I can't follow your workbook, but a two-value data table may be what you want.

It's well explained in Help, but basically you create a table with one input variable across the top (e.g., B1:F1), and the other input varaible down the left column (e.g., A2:A5).

In the upper-left corner (A1 in this example), you put a formula. That formula can be (usually is) a simple reference to some other cell which may the result of a whole workbook full of calculations based on B1 and A2.

When you compute the table, Excel will substitute the values from the top row and left column into the whole model and output the result in the corresponding row and column of the body of the table.

[JBeaucaire]

On the first sheet, columns X and Y are where I want to get the results from the calculator, which is titled blow down volume calculator. The values in columns V and W on the sheet Gas Loss Report are the inputs for the calculator...

...where do those values plug into the calculator?

... and the values I want to get back are in cells M26 and L27 on the calculator sheet.

OK, easy enough just need instructions exactly about what goes where.

[Myer]

The input for the calculator is in cell D8 (on that sheet of course). And the values I'd like returned are calculated in cells M26 and L27 (Calculator sheet again). On the gas loss report sheet there are two columns one titled "Launcher Gas Loss/MCF" and the other "Receiver Gas Loss/MCF" The values calculated in M26 correspond to the Receiver and in L27, to the launcher. Hope this helps.

[Myer]

Anyone have any success getting this to work??

[JBeaucaire]

I don't mean to be dense, but your instructions are piecemeal and non-comprehensive. You need to give them to us in a step-by-step manner so we have to interpret as little as possible.

1) GLR cell V3...value goes to BDVC cell ??
2) GLR cell W3...value goes to BDVC cell D8??

Then...
3) BDVC cell M26 value goes to GLR cell Y3??
4) BDVC cell L27 value goes to GLR cell X3???

5) Repeat with next row of GLR data (V4, W4)

[Myer]

GLR cell V3 value goes to BDVC cell D8 as does GLR cell W3. The computations will have to be performed separately for GLR V3 and W3 because there is only one input cell. This can easily be changed to accommodate both V3 and W3 simultaneously if that would make things easier.

Yes, BDVC cell M26 goes to GLR Y3 and BDVC L27 goes to X3. Then repeat.

[JBeaucaire]

Well, we're still not clear then.

1) GLR V3 goes to BDVC D8.

Results from M26 go back to X3
Results from L27 go back to Y3

2) GLR W3 goes to BDVC D8.

Results from M26 go back to X3??? This overwrites previously saved values
Results from L27 go back to Y3??? This overwrites previously saved values

[Myer]

When GLR V3 is plugged into BDVC D8, the only result needed is BDVC M26, which is then copied to GLR X3.

When W3 is plugged into BDVC D8, the result of interest is calculated in BDVC L27, which I'd like to copy to Y3. The repeat for subsequent rows.

In short, every individual input yields only one output. All values headed with Launcher go together and all headed Receiver go together.

Hope this makes since...

[JBeaucaire]

This will do it, then:

Option Explicit

Sub RunCalculator()
Dim LastRw As Long, Rw As Long
Dim BDVC As Worksheet, GLR As Worksheet

Set GLR = Sheets("Gas Loss Report")
Set BDVC = Sheets("Blow Down Volume Calculator")

LastRw = GLR.Range("V" & Rows.Count).End(xlUp).Row
LastRw = Application.WorksheetFunction.Max(LastRw, _
            GLR.Range("W" & Rows.Count).End(xlUp).Row)
            
    For Rw = 3 To LastRw
        BDVC.Range("D8").Value = GLR.Range("V" & Rw).Value
        GLR.Range("X" & Rw).Value = BDVC.Range("M26").Value
        BDVC.Range("D8").Value = GLR.Range("W" & Rw).Value
        GLR.Range("Y" & Rw).Value = BDVC.Range("L27").Value
    Next Rw

End Sub