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!
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!