VBA: Loop through column values and store

Question

I have constructed a financial model in excel, but want to know the sensitivity of a large set of assumptions. I have a tab of assumptions, which feeds into the model (col D8:D235), in the same sheet, I have made some alterations of these assumptions (Col M : Col R).

I basically want to replace the values in col D with the values in each of these columns in a loop, and save the corresponding output values. Within a for-loop, I would like to change the values in column D of the assumption sheet with column M, N, O, P, etc.., to retrieve output values which are created in the "Output" sheet in I28:I57, which I would subsequently like to store in column K, L, etc. before replacing the values in the assumption sheet (column D), with the next set of input values.

I have zero knowledge with VBA, so I was hoping someone could help me out.

To illustrate, I think I now correctly call out the columns which are involved, but have no clue how to write the 'for-loop' in VBA.

Sub SensitivityAnalysis()
    'Define variables
    Dim inputVariables As Range
    Dim inputSet As Range
    Dim initialVariables As Range
    Dim Output As Range
    Dim outputTable As Range
    Dim i As Integer
    
    
    'Set input and output ranges
    Set inputVariables = Worksheets("Assumption sheet").Range("D8:D235")
    Set inputSet = Worksheets("Assumption sheet").Range("M8:R235")
    Set Output = Worksheets("Output sheet").Range("I28:I57")
    Set outputTable = Worksheets("Output sheet").Range("K28:P57")

    For i = 1:6
          inputVariables.clear()
          inputVariables = inputSet(i) 
          outputTable(i) = Output
End Sub

Thanks in advance.

I tried writing a for loop, but failed to do so

Answer 1

Please, test the next piece of code. It iterates between all columns of inputSet, copy each column overwriting existing in inputVariables, calculates the output sheet and saves the output in the corresponding column:

Sub ProcessAssumptions()
 Dim WB As Workbook, wsAss As Worksheet, wsOut As Worksheet, inputVariables As Range, inputSet As Range
 Dim Output As Range, outputTable As Range, i As Long
 
 Set WB = ActiveWorkbook 'use here the workbook you need
 Set wsAss = WB.Worksheets("Assumption sheet")
 Set wsOut = WB.Worksheets("Output sheet")
 
 Set inputVariables = wsAss.Range("D8:D20") ' wsAss.Range("D8:D235")
 Set inputSet = wsAss.Range("M8:R20") ' wsAss.Range("M8:R235")
 
 Set Output = wsOut.Range("I28:I32") 'wsOut.Range("I28:I57")
 Set outputTable = wsOut.Range("K28:P32") 'wsout.Range("K28:P57")
 
 'Iterate between inputSet columns and do processing:
 For i = 1 To inputSet.Columns.count
    inputVariables.value = inputSet.Columns(i).value 'copy the inputSet as value, not as formula
    wsOut.Calculate     'if other sheets are involved you should use WB.Calculate
    outputTable.Columns(i).value = Output.value      'save the output in the corresponding colummn
 Next i
 
 MsgBox "Ready..."
End Sub