Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update cell values after cell is changed
Hello, I need help with some code for IF then Else.
I am trying to look at amounts in cells b2, b3, b4 for a number of certain days. When each one of these cells is changed, the amount in the formula will be changed accordingly. I can only get this to work if I use below 30 days... I cannot use a private sub, since this will be used over and over again in different workbooks. Is there something wrong with my If statement?? Public Sub Renewal() Dim Intx As Integer Dim lngRow As Long Dim lastrow As Long Dim iCol As Long Dim iRow As Long Dim unit1 As Integer Dim unit2 As Integer Dim unit3 As Integer Cells(1, 1).Select unit1 = Range("$b$2").Value unit2 = Range("$b$3").Value unit3 = Range("$b$4").Value 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 'looks to see if row above formula cell is 0 If .Cells(iRow - 1, iCol).Value = 0 _ And IsEmpty(.Cells(iRow - 1, iCol)) = False Then If .Cells(iRow, iCol - 1).Value = 0 _ And IsEmpty(.Cells(iRow, iCol - 1)) = False _ And unit1 + .Cells(iRow - 1, iCol).Value + _ .Cells(iRow - 1, iCol - 1).Value + .Cells(iRow - 1, iCol + 2) < 90 _ And unit1 + .Cells(iRow - 1, iCol).Value + ..Cells(iRow - 1, iCol + 1) _ + .Cells(iRow - 1, iCol + 2).Value 60 Then .Cells(iRow, iCol + 2).Formula = "=VLOOKUP(""appw1d"",$f$2:$g$4,2,FALSE)*(1-($b$2-60)/30)" ElseIf .Cells(iRow, iCol - 1).Value = 0 _ And IsEmpty(.Cells(iRow, iCol - 1)) = False _ And unit1 + .Cells(iRow + 1, iCol).Value + ..Cells(iRow + 1, iCol + 1).Value < 60 _ And unit1 + .Cells(iRow + 1, iCol).Value + ..Cells(iRow + 1, iCol + 1).Value 30 Then .Cells(iRow, iCol + 1).Formula = "=VLOOKUP(""appw1d"",$f$2:$g$4,2,FALSE)*(1-($b$2-30)/30)" This section works correctly ElseIf .Cells(iRow, iCol - 1).Value = 0 _ And IsEmpty(.Cells(iRow, iCol - 1)) = False _ And unit1 + .Cells(iRow + 1, iCol).Value < 30 _ And unit1 + .Cells(iRow + 1, iCol).Value 0 Then .Cells(iRow, iCol).Formula = "=VLOOKUP(""appw1d"",$f$2:$g$4,2,FALSE)*(1-$b$2/30)" ElseIf .Cells(iRow + 1, iCol).Value = 0 _ And IsEmpty(.Cells(iRow + 1, iCol)) = False Then .Cells(iRow, iCol).Formula = "=VLOOKUP(""appw1d"",$f$2:$g$4,2,FALSE)" End If End If Next iCol End If End If Next iRow Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to clear cell values, when a different cell is changed. | Excel Discussion (Misc queries) | |||
Update date & time in a cell only when worksheet is changed | Excel Discussion (Misc queries) | |||
Tab Names to Match Cell Values when Changed | Excel Discussion (Misc queries) | |||
Cell values have changed | Excel Discussion (Misc queries) | |||
update formula in column when another cell formula is changed | Excel Worksheet Functions |