Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula calculation when cells are changed
Hi,
I am trying to perform a calculation based on Range b2 in a workbook. However, I want the user to be able to change this cell and not have to rerun the macro again. I have an if...then statement that evaluates less than 60 and greater than 30, then it evaluates less than 30, etc. Is there a way I can do this? Here is my code:(with help from a very nice person!) With ActiveSheet For iRow = 2 To lngRow If InStr(1, .Cells(iRow, 2).Value, "appw1d", vbTextCompare) Then If LCase(.Cells(iRow, 1).Value) = LCase(" Target Renewal") Then For iCol = 6 To 20 If Range("$b$2") + .Cells(iRow - 1, iCol).Value + .Cells(iRow - 1, iCol + 1).Value < 60 _ And .Cells("$B$2") + .Cells(iRow - 1, iCol).Value + .Cells(iRow - 1, iCol + 1).Value 30 Then .Cells(iRow, iCol + 1).Formula = "=VLOOKUP(""appw1d"",$f$1:$g$16,2,FALSE)*(1-$b$2-30)/30" ElseIf .Cells(2, 2).Value + .Cells(iRow - 1, iCol).Value < 30 Then .Cells(iRow, iCol).Formula = "=VLOOKUP(""appw1d"",$f$1:$g$16,2,FALSE)*(1-$b$2/30)" Else ..Cells(iRow, iCol).Formula = "=VLOOKUP(""appw1d"",$f$1:$g$16,2,FALSE)" Next iCol End If End If Next iRow End With Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Formula Calculation to add some cells | Excel Discussion (Misc queries) | |||
the calculation tab being changed and you didn't change it? | Excel Discussion (Misc queries) | |||
keep specific cells from being changed. | Excel Discussion (Misc queries) | |||
My Calculation Mode Changed to Manual Somehow?!? | Excel Discussion (Misc queries) | |||
Save only new or changed cells | Excel Programming |