PDA

View Full Version : VBA loop through formula



huaya
11-24-2008, 04:01 PM
I want the formula in cell B7 same as the the formula in B8, which is =min(7%,max(A1+1.5%,1.5%)). The numbers in formula B8 is same as = min(C4, max(D4, B4)). I do not want the reference cells in the formula, since I have to update the Cell B7 in another spreadsheet. In the new spreadsheet, A1 will be changed to H1. And I will have to update H1 every month.

Please see attached spreadsheet for detailed example.

I want to create a VBA loop through formula which I can show the real number based on column B, C and D other than the reference cells. Please also see the detailed example in sheet 2. I would like to have the forumla as in column E in sheet 2, based on column B, C and D. Column F is the example of the formula based on the reference cells.

Thank you very much for your help.

Kenneth Hobs
11-24-2008, 08:43 PM
If I understand what you want:
Sub SetColE()
Dim c As Range
For Each c In Worksheets("Sheet2").Range("E2", _
Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp))
c.Formula = "=MIN(" & c.Offset(0, -2) & ",MAX(" & _
Right(c.Offset(0, -1).Formula, Len(c.Offset(0, -1).Formula) - 1) & "," _
& c.Offset(0, -3) & "))"
Next c
End Sub

huaya
11-25-2008, 06:00 PM
It works perfectly. Have a great night. :hi: