Reputation: 121
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
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