CODEWITHSUNDEEP
excelvba
Zach
Zach

Reputation: 341

Running multiple values through excel calculation

Another member of my team has built the elaborate spreadsheet that calculates a certain value we need. However, it's currently set up like so:

My task is to generate a series of what ifs. If A1 = 4, A2 = 4.7, A3 = 0.2, what would D1 be?

Repeat that for the 10k+ other valid combinations of A1, A2, and A3 while logging the outputs.

My instinct was just to treat it like a function in a programming language and make it a function that takes the inputs and spits out an output... however I'm not sure how to do that. Any guidance on how this can be done? The function the coworker runs is very complicated, so it's not easy to replicate that logic across multiple rows or columns as far as "drag and drop" like I might do for a SUM or something.

Upvotes: 0

Views: 1779

Answers (1)

Zach
Zach

Reputation: 341

With thanks to Michael Murphy in the comments which gave me some things to search. I'm leaving this here so future people searching can find it.

Sub test()
'
' test Macro
'
    Dim a_array As Variant
    Dim b_array As Variant
    Dim c_array As Variant
    Dim rw_cnt As Integer

    rw_cnt = 2
    ' This selects each of the things to combo
    a_array = ThisWorkbook.Sheets(2).Range("A2:A5")
    b_array = ThisWorkbook.Sheets(2).Range("B2:B5")
    c_array = ThisWorkbook.Sheets(2).Range("C2:C5")

    For Each a_el In a_array
        For Each b_el In b_array
            For Each c_el In c_array
                worth = compute_worth(a_el, b_el, c_el)
                ' This writes the output where we can track it
                ThisWorkbook.Sheets(2).Cells(rw_cnt, 6) = a_el
                ThisWorkbook.Sheets(2).Cells(rw_cnt, 7) = b_el
                ThisWorkbook.Sheets(2).Cells(rw_cnt, 8) = c_el
                ThisWorkbook.Sheets(2).Cells(rw_cnt, 9) = worth
                rw_cnt = rw_cnt + 1
            Next a_el
        Next b_el
    Next c_el
End Sub


Function compute_worth(a, b, c)
    ' This puts the value into the original sheet then extracts the output
    ThisWorkbook.Sheets(1).Range("A1") = a
    ThisWorkbook.Sheets(1).Range("A2") = b
    ThisWorkbook.Sheets(1).Range("A3") = c
    worth = ThisWorkbook.Sheets(1).Range("D1").Value
    compute_worth = worth
End Function

Upvotes: 1

Related Questions