0

There are 3 worksheets ('directions', 'calculation', 'combinations') in a single Excel file, each looks like the following:

'directions' sheet: 'directions' sheet
this is the only sheet the user should have interaction with; the user basically copies information about sheet 'calculation'

'calculation' sheet: 'calculation' sheet
this sheet contains list of inputs (simple integers) and outputs (I've added column G which explain which formulas are used to calculate outputs from column F)

The aim is to create a VBA script that inserts values from 'directions' column C into 'calculations' column D, then extracts resulting outputs from column F of 'calculations' and saves everything in a single table in sheet 'combinations'. The resulting table should basically have all combinations of all inputs and respective outputs, looking like: this

The problem is this solution is not scalable, meaning that now it works only with exactly 3 inputs and 4 outputs. With a different number of inputs/outputs, the script would require manual adjustment to work properly (adding new variables, adding new layers to FOR loop, etc.). Is there a way to make this script self-adjustable for any number of inputs and outputs?

The script I've used here:

Sub AllCombinations()

Application.ScreenUpdating = False

Dim thisWB As Workbook
Dim sheetDirections As Worksheet, sheetCalc As Worksheet, sheetComb As Worksheet
Dim input1 As Integer, input2 As Integer, input3 As Integer
Dim output1 As Integer, output2 As Integer, output3 As Integer, output4 As Integer
Dim inputValues1() As String, inputValues2() As String, inputValues3() As String
Dim inputCoords1 As String, intputCoords2 As String, inputCoords3 As String
Dim outputCoords1 As String, outputCoords2 As String, outputCoords3 As String
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim rowNr As Integer

Set thisWB = ThisWorkbook
Set sheetDirections = thisWB.Worksheets("directions")
Set sheetCalc = thisWB.Worksheets("calculation")
Set sheetComb = thisWB.Worksheets("combinations")

' save coordinates of input/output
inputCoords1 = sheetDirections.Cells(2, 2).Value
inputCoords2 = sheetDirections.Cells(3, 2).Value
inputCoords3 = sheetDirections.Cells(4, 2).Value
outputCoords1 = sheetDirections.Cells(2, 6).Value
outputCoords2 = sheetDirections.Cells(3, 6).Value
outputCoords3 = sheetDirections.Cells(4, 6).Value
outputCoords4 = sheetDirections.Cells(5, 6).Value

' clear whole sheet before use
sheetComb.Cells.Clear

' print names in first row
sheetComb.Cells(1, 1).Value = sheetDirections.Cells(2, 1).Value
sheetComb.Cells(1, 2).Value = sheetDirections.Cells(3, 1).Value
sheetComb.Cells(1, 3).Value = sheetDirections.Cells(4, 1).Value
sheetComb.Cells(1, 4).Value = sheetDirections.Cells(2, 5).Value
sheetComb.Cells(1, 5).Value = sheetDirections.Cells(3, 5).Value
sheetComb.Cells(1, 6).Value = sheetDirections.Cells(4, 5).Value
sheetComb.Cells(1, 7).Value = sheetDirections.Cells(5, 5).Value

' split input variables, separator is ';'
inputValues1 = Split(sheetDirections.Range("C2").Value, ";")
inputValues2 = Split(sheetDirections.Range("C3").Value, ";")
inputValues3 = Split(sheetDirections.Range("C4").Value, ";")

' input/output calculation and printing
rowNr = 2
For i1 = 0 To UBound(inputValues1)
    For i2 = 0 To UBound(inputValues2)
        For i3 = 0 To UBound(inputValues3)
            ' inputs - print
            sheetComb.Cells(rowNr, 1).Value = inputValues1(i1)
            sheetComb.Cells(rowNr, 2).Value = inputValues2(i2)
            sheetComb.Cells(rowNr, 3).Value = inputValues3(i3)
            ' outputs - insert
            sheetCalc.Range(inputCoords1).Value = inputValues1(i1)
            sheetCalc.Range(inputCoords2).Value = inputValues2(i2)
            sheetCalc.Range(inputCoords3).Value = inputValues2(i3)
            ' outputs - print
            sheetComb.Cells(rowNr, 4).Value = sheetCalc.Range(outputCoords1).Value
            sheetComb.Cells(rowNr, 5).Value = sheetCalc.Range(outputCoords2).Value
            sheetComb.Cells(rowNr, 6).Value = sheetCalc.Range(outputCoords3).Value
            sheetComb.Cells(rowNr, 7).Value = sheetCalc.Range(outputCoords4).Value

            rowNr = rowNr + 1
        Next i3
    Next i2
Next i1

Application.ScreenUpdating = True

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
rosu
  • 1
  • You're already dealing with arrays, `inputValues1` is an array - I recommend creating arrays for your inputCoords and outputCoords e.g. `Dim inputCoords As Variant: inputCoords = sheetDirections.Range("A2:A5").Value2` - you can get the dynamic range easily, just look for "find last row excel vba" on Google to get started. Then you can loop through `inputCoords` like you do with `inputValues1` – jamheadart May 15 '20 at 10:39
  • Thanks @jamheadart, makes sense storing those in an array. But how do I do the nested loop in the end? Since I have to loop through all splitted elements of all ```inputValue```, and different ```inputValue``` elements can be of different length, how should I loop through each combination? – rosu May 15 '20 at 13:09
  • [Here](https://stackoverflow.com/questions/31472816) you can find an almost identical task of generating combinations with any number of columns/options just the the input range format there is slightly different. In your case the bottleneck (if you expect a lot of rows) can be filling output rows one by one with your current approach. – BrakNicku May 15 '20 at 15:04

0 Answers0