Mammoth Insert Formula

[Guest]

Bob,

I had to add another column to update the formula. I understood your
instructions and believe I did it correctly. At least I did not get an error
messeage, but now it is not working. My thought is that perhaps there is
something I need to add because of the Indirect or vloopup statements?

Thanks for your help!

If Range("B26").Value = "Yes" Then
For iCtr = 36 To LastRow
'update the Pro-Rata Share formula
CAMYes = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr &
"C10" & _
"=""No"",0,IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(R6C2=0,(R" & iCtr & "C11* R" & iCtr & _
"C9),(R" & iCtr & "C11* R" & iCtr &
"C9)/365*(R6C2)))))"
Me.Range("L" & iCtr).FormulaR1C1 = CAMYes

'update the Pro-Rata Share Percentage
PercentYes = "=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 = PercentYes
Next
ElseIf Range("B26").Value = "No" Then
For iCtr = 36 To LastRow
'update the Pro-Rata Share formula
CAMNo = "=IF(ISBLANK(R" & iCtr & "C10),"""",IF(R" & iCtr &
"C10" & "=""No""," & _
"IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(R6C2=0,(R" & iCtr & "C11* R" & iCtr & _
"C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R6C2))))"
Me.Range("L" & iCtr).FormulaR1C1 = CAMNo

'update the Pro-Rata Share Percentage
PercentNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _
"Indirect(Vlookup('Line Items'!!$R" & iCtr - 21 &
"C3,CAMPerCentLoc,3,False)))"
Me.Range("K" & iCtr).FormulaR1C1 = PercentNo
Next
End If
End If

 

[Guest]

Bob,

I commented out everything I added and it still is not working. I am
attaching the whole routine. The CAP Yes/Nos work fine but the rest of it
does not. It hesitates like it is doing something but no changes are made.
Could there be anything that would cause it to work intermitantly?

Thanks,
Karen


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 PercentYes As String
'Dim PercentNo 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

' Call EndRange(LastRow)

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(R6C2=0,(R" & iCtr & "C11* R" & iCtr & _
"C9),(R" & iCtr & "C11* R" & iCtr &
"C9)/365*(R6C2)))))"
Me.Range("L" & iCtr).FormulaR1C1 = sYes

'update the Pro-Rata Share Percentage
' PercentYes = "=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 = PercentYes
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(R" & iCtr &
"C10" & "=""No""," & _
"IF(ISNUMBER(R" & iCtr & "C16),R" & iCtr & _
"C16,IF(R6C2=0,(R" & iCtr & "C11* R" & iCtr & _
"C7),(R" & iCtr & "C11* R" & iCtr & "C7)/365*(R6C2))))"
Me.Range("L" & iCtr).FormulaR1C1 = sNo

'update the Pro-Rata Share Percentage
' PercentNo = "=IF(ISBLANK(R" & iCtr & "C10),""""," & _
"Indirect(Vlookup('Line Items'!!$R" & iCtr - 21 &
"C3,CAMPerCentLoc,3,False)))"
' Me.Range("K" & iCtr).FormulaR1C1 = PercentNo
Next
End If
End If

'Set the CAP label
'If Range("B28").Value = "Yes" Then
' Range("J23").Value = "CAP"
' Else
' Range("J23").Value = ""
' End If

'Set the Base Year Adj label
'If Range("B29").Value = "Yes" Then
' Range("J24").Value = "Base Year Adj"
'Else
' Range("J24").Value = ""
' End If

'Set the Minimum CAP Label
'If Range("B30").Value = "Yes" Then
' Range("J25").Value = "Minimum CAP"
' Else
' Range("J25").Value = ""
' End If

ws_exit:
Application.EnableEvents = True
End Sub