Update cell values after cell is changed

T

TR

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)"


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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top