CODEWITHSUNDEEP
excelvba
zafira.404
zafira.404

Reputation: 121

Loop through two or more range of cells to show next number in the sequence as cell's new value

I have an Excel formula which is dependent on two defined ranges, Interest_Range and Deposit, and I have two cells which hold value for the Formula to use, A7(Interest_Range) and A8(Deposit). The formula '=SUM(B3/Interest_Range*Deposit' is located in A6. Once the formula is executed for each number in both ranges, the result is recorded in another Sheet. I now know how to achieve it with a single range, but looking for a solution to allow for multiple ranges.

Solution for a single range:

Sub tgr()

    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsDest As Worksheet
    Dim rInterestCell As Range
    Dim rDest As Range

    Set wb = ActiveWorkbook
    Set wsData = wb.Sheets("Sheet1")
    Set wsDest = wb.Sheets("Formula Results")

    For Each rInterestCell In Range("Interest_Range").Cells
        wsData.Range("A7").Value = rInterestCell.Value  'Put the interest cell value in range A7, which is used by the formula in A6
        wsData.Calculate    'Update the formula result based on the new value in A7
        Set rDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
        If rDest.Row < 6 Then Set rDest = wsDest.Range("A6")    'Guarantee that A6 is the starting cell for the results
        rDest.Value = wsData.Range("A6").Value   'Put the value only in a new row in the destination sheet
    Next rInterestCell

End Sub

Upvotes: 0

Views: 109

Answers (1)

tigeravatar
tigeravatar

Reputation: 26670

Something like this should work for you. Note that it outputs in "Formula Results" sheet in columns A, B, and C. Column A result is the interest cell value used, column B result is the Deposit cell value used, and column C result is the formula result based on those two values.

Sub tgr()

    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsDest As Worksheet
    Dim rInterestCell As Range
    Dim rDepositCell As Range
    Dim rDest As Range

    Set wb = ActiveWorkbook
    Set wsData = wb.Sheets("Sheet1")
    Set wsDest = wb.Sheets("Formula Results")

    For Each rInterestCell In Range("Interest_Range").Cells
        wsData.Range("A7").Value = rInterestCell.Value  'Put the interest cell value in range A7, which is used by the formula in A6
        For Each rDepositCell In Range("Deposit").Cells
            wsData.Range("A8").Value = rDepositCell.Value   'Put the deposit cell value in range A8, which is used by the formula in A6
            wsData.Calculate    'Update the formula result based on the new values in A7 and A8

            'Guarantee that A6 is the starting cell for the results
            Set rDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1)
            If rDest.Row < 6 Then Set rDest = wsDest.Range("A6")

            'Output the interest cell value used, the deposit cell value used, and the formula result with those values
            rDest.Resize(, 3).Value = Array(rInterestCell.Value, rDepositCell.Value, wsData.Range("A6").Value)
        Next rDepositCell
    Next rInterestCell

End Sub

Upvotes: 1

Related Questions