Bob,
I feel like I am going nuts. This was working this morning. I saved it and
worked in another part of the workbook which at this point is unrelated to
this sheet. I go back to this sheet and this no longer works. I don't get
what is happening. It is impossilble for ictr to be less than 21 as it
starts at 36. I need to understand why this happened. I've gone through this
again trying to find the problem. I've messed with it now and probalby made
it worse but I can't see what is or was wrong. I'm getting pretty
frustrated. Why would it work and then not work when I hadn't made any
changes to this code? Here is the complete routine.
Thanks,
Karen
Option Explicit
'
'
'Master CAM worksheet procedures
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B26"
Dim iCtr As Long
Dim sNo As String
Dim sYes As String
Dim sPercentYes As String
Dim sPercentNo As String
Dim LastRow As Long
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
LastRow = 337
If Range("B26").Value = "Yes" Then
For iCtr = 36 To LastRow
'update the Pro-Rata Share formula
sYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr &
"C10" & _
"=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(ISBLANK(R3C2),(R" & iCtr & "C11* R" & iCtr & _
"C9),(R" & iCtr & "C11* R" & iCtr &
"C9)/365*(R3C2)))))"
Me.Range("L" & iCtr).FormulaR1C1 = sYes
'original formula
=IF(ISBLANK($J36),"",IF($J36="No",0,IF(ISNUMBER($P36),$P36,IF(ISBLANK($B$3),($K36*$I36),($K36*$I36)/365*($B$3)))))
Next
ElseIf Range("B26").Value = "No" Then
For iCtr = 36 To LastRow
'update the Pro-Rata Share formula
sNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _
"IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(ISBLANK(R3C2),(R" & iCtr & "C11* R" & iCtr & _
"C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R3C2))))"
Me.Range("L" & iCtr).FormulaR1C1 = sNo
'original formula
=IF(ISBLANK($J36),"",IF(ISNUMBER($P36),$P36,IF(ISBLANK($B$3),($K36*$G36),($K36*$G36)/365*($B$3))))
Next
End If
End If
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
LastRow = 337
If Range("B26").Value = "Yes" Then
For iCtr = 36 To LastRow
'update the Pro-Rata Share Percentage - line items begin on
row 15, so 36-15 = 21
sPercentYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" &
iCtr & "C10" & "=""No""," & _
"0, Indirect(Vlookup('Line Items'!R" & iCtr - 21 &
"C3,CAMPerCentLoc,3,False))))"
Me.Range("K" & iCtr).FormulaR1C1 = sPercentYes
'original formula
=IF(ISBLANK(J36),"",IF(J36="No",0,INDIRECT(VLOOKUP('Line
Items'!$C15,CAMPerCentLoc,3,FALSE))))
Next
ElseIf Range("B26").Value = "No" Then
For iCtr = 36 To LastRow
'update the Pro-Rata Share Percentage - line items begin on
row 15, so 36-15 = 21
sPercentNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _
"Indirect(Vlookup('Line Items'!R" & iCtr - 21 &
"C3,CAMPerCentLoc,3,False)))"
Me.Range("K" & iCtr).FormulaR1C1 = sPercentNo
'original formula =IF(ISBLANK(J36),"",INDIRECT(VLOOKUP('Line
Items'!$C15,CAMPerCentLoc,3,FALSE)))
Next
End If
End If
If Not Intersect(Target, Me.Range("B28:B30")) Is Nothing Then
'Set the CAP label
If Range("B28").Value = "Yes" Then
Me.Range("J23").Value = "CAP"
Else
Me.Range("J23").Value = ""
End If
'Set the Base Year Adj label
If Range("B29").Value = "Yes" Then
Me.Range("J24").Value = "Base Year Adj"
Else
Me.Range("J24").Value = ""
End If
'Set the Minimum CAP Label
If Range("B30").Value = "Yes" Then
Me.Range("J25").Value = "Minimum CAP"
Else
Me.Range("J25").Value = ""
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub
