How do I input values in the Excel model without doing it manually?

Question

I have the feeling that my previous question is being misunderstood. Therefore, I will do this as follows. What I would like is the following:

In this picture you see that the input is 4 in cell B1. This B1 cell is used as an input for a complicated model in a different sheet. The output of that model is linked to B2. I do not want to modify the complicated model on the other sheet (this part is required).

Now I would like to create a table like this:

So when the input is 4, the output from the model in the other sheet is 1. Now I can do this manually for 1, 2 and 3, by simply replacing the number written at B2 (which is 4) to 1 and check its output. Let’s observe what our complicated model gives us:

Apparently it gives us a 9, so I fill 9 in the F2. Now this is currently not a problem, since my table goes from 1 to 4. But how do I automate this? For example if I go from 1 to 100.

The problem is, you cannot touch or simplify the model on the other sheet.

How would one do this in VBA or do this in Excel itself?

Thank you for your response, however this was not exactly what I meant I think. I have updated the question with images. The formula was just for illustration and had nothing to do with the question. — Snowflake, Mar 02 '17 at 18:09

score 2 · Answer 1 · answered Mar 02 '17 at 17:37

I think you might have an issue in your explanation. Your example is A1 = 1, B1 = A1+5, despite saying it's A1 & A2. You are saying "Columns C & D" when I believe you mean "Rows 3 & 4).

If I understand your plight correctly, you are looking to drag down (auto-fill) the formula to the subsequent rows. You can do this with VBA, but you can do it without.

Provided you have data in the first column (A) as far as you would like the formula to travel, you can double-click from the lower right hand corner of the formula-reference cell and it will fill down. You could also drag the formula down by clicking and holding the lower right hand corner of the formula-reference cell.

You will know if you have the lower right hand corner if your cursor changes from from a "+" that is relatively large (with interior color) to a "+" that is relatively small (with no interior color... all black).

You will need to ensure that you have relative references when doing this, or ensure that your non-relative references are what you want. This is more than your question asked, but is important when doing this type of work.

See this:

Range("B1").Formula = A1 + 5

In this formula, A1 is relative to B1 by an off-set of -1 column. Every cell that the formula is pasted or dragged into will perform the formula with the cell that is -1 column relative.

You can add specifics to columns, rows, or cells, by use of "$". Say you have your example, and want to show the formula in Column C. There're 3 scenarios by using "$" which have different outcomes:

Fully relative, the dragged-formula will automatically designate the adjacent column.

Range("B1").Formula = A1 + 5
Range("C1").Formula = B1 + 5
Range("B2").Formula = A2 + 5
Range("C2").Formula = B2 + 5

If the "$" is in front of the column in the formula, the-dragged will "lock" the column, so when the formula is dragged, the column stays the same, but the row number will change.

Range("B1").Formula = $A1 + 5
Range("C1").Formula = $A1 + 5
Range("B2").Formula = $A2 + 5
Range("C2").Formula = $A2 + 5

If the "$" is in front of the row in the formula, the-dragged will "lock" the row, so when the formula is dragged, the row stays the same, but the column will change.

Range("B1").Formula = A$1 + 5
Range("C1").Formula = B$1 + 5
Range("B2").Formula = A$1 + 5
Range("C2").Formula = B$1 + 5

If the "$" is in front of the each the column and row in the formula, the-dragged will "lock" both. When the formula is dragged, the referenced-cell stays the same.

Range("B1").Formula = $A$1 + 5
Range("C1").Formula = $A$1 + 5
Range("B2").Formula = $A$1 + 5
Range("C2").Formula = $A$1 + 5

Hopefully that helps and is what you're looking for!

score 1 · Accepted Answer · answered Mar 02 '17 at 20:00

So you want to automate putting values through your model and recording the outputs.

A very simple approach begins with putting your list of inputs in column E as in your example picture. Note down the start and end row numbers - 2 and 5 in your example (as your input values are in the range E2:E5).

In the VBA editor created a new sub in a new module to hold your code (you can Google how to do this). The code is fairly simple.

Sub TurnInputsIntoOutputs()

    ' First we create some useful and important variables
    Dim inputStartRow As Long  ' The row your input values start on
    Dim inputEndRow As Long  ' The row your input values end on
    Dim currentRow As Long  ' A placeholder for when we cycle through the rows
    Dim processingWorksheet As Worksheet  ' How we're going to reference your worksheet

    ' Then we put values into those variables
    inputStartRow = 2
    inputEndRow = 5
    Set processingWorksheet = ThisWorkbook.Sheets("*the name of your worksheet here*")

    ' Now we cycle through the input rows
    For currentRow = inputStartRow to inputEndRow
        ' Put input value from the current row of column E into cell B1
        processingWorksheet.Range("B1").Value = processingWorksheet.Range("E" & currentRow).Value

        ' Put the resulting output value into the current row of column F
        processingWorksheet.Range("F" & currentRow).Value = processingWorksheet.Range("B2").Value
    Next currentRow

End Sub

And then just run your macro (you can link it to a Form button or just run it from the VBE - easy enough for your to Google how to do).

How do I input values in the Excel model without doing it manually?

2 Answers2