PDA

View Full Version : Excel - 2003 VBA Code Error - Run time error '380':Invalid Property



Vedant
07-05-2008, 12:22 PM
Dear Sir/Madam,

I new in this field and it?s my job first month, I am getting the following error. I would appreciate if you would help me. In excel-2003 VBA Code I am getting ?Run time error '380':Invalid Property?. I tried all possibilities but couldn?t succeed.

I have put ** in the code where I am getting error.

Thanks a lot
Vedant


'This subroutine updates
Sub runRenewals()
Dim RenewalDate As Date
Dim ReportDate As Date
Dim colRenewalCaseChar As Collection
Dim colPremium As Collection
Dim colClaims As Collection
Dim colMisc As Collection
Dim i As Long

RenewalDate = getRenewalDate
ReportDate = getReportDate

Set colRenewalCaseChar = getRenewalGroups(RenewalDate, ReportDate)
Range("EraseCaseChar").ClearContents
Range("ErasePremium").ClearContents
Range("EraseIncurredPaidClaims").ClearContents

Range("Control").Select
Sheet2.pbProgress.Min = 1
** Sheet2.pbProgress.Max = colRenewalCaseChar.Count ' Run time error '380':Invalid property


For i = 1 To colRenewalCaseChar.Count
Set colPremium = New Collection
Set colClaims = New Collection
Set colMisc = New Collection

Set colPremium = getGroupsPremium(colRenewalCaseChar(i).getGroupNumber)
Set colClaims = getGroupClaims(colRenewalCaseChar(i).getGroupNumber)
Set colMisc = getGroupsCaseChar(colRenewalCaseChar(i).getGroupNumber)
Call UpdateCaseChar(colRenewalCaseChar(i), i)
Call UpdatePremium(colPremium, i)
Call UpdateClaims(colClaims, i)
Call UpdateMisc(colMisc, i)
Sheet2.pbProgress.Value = i
Next i

Sheet1.Visible = True
Sheet3.Visible = True
Sheet7.Visible = True

End Sub

Sub UpdateCaseChar(MyCaseChars As CaseChar, i As Long)
Dim rCaseChar As Range
Set rCaseChar = Range("StartCaseChar")
rCaseChar(i, 1).Value = i
rCaseChar(i, 2).Value = MyCaseChars.getGroupNumber
rCaseChar(i, 3).Value = MyCaseChars.getDatabase
rCaseChar(i, 4).Value = MyCaseChars.getState
rCaseChar(i, 5).Value = MyCaseChars.getEmployerName
rCaseChar(i, 6).Value = MyCaseChars.getWritingAgent
rCaseChar(i, 7).Value = MyCaseChars.getGeneralAgent

rCaseChar(i, 8).Value = MyCaseChars.getOriginalEffectiveDate
rCaseChar(i, 9).Value = MyCaseChars.getTotalEEs
rCaseChar(i, 10).Value = MyCaseChars.getMembers
rCaseChar(i, 11).Value = MyCaseChars.getRTNB
rCaseChar(i, 12).Value = MyCaseChars.getNextRenewalDate
rCaseChar(i, 13).Value = MyCaseChars.getLastRenewalDate
rCaseChar(i, 14).Value = MyCaseChars.getLastIncrease
rCaseChar(i, 15).Value = MyCaseChars.getPlan
rCaseChar(i, 16).Value = MyCaseChars.getTakeover
rCaseChar(i, 17).Value = MyCaseChars.getSIC
rCaseChar(i, 18).Value = MyCaseChars.getDeductible
rCaseChar(i, 19).Value = MyCaseChars.getCommission
End Sub

Sub UpdatePremium(colPrem As Collection, i As Long)
Dim myobjPremium As Premium
Dim rPremium As Range
Dim dDate As Date

Dim c As Long
If colPrem.Count > 0 Then
Set rPremium = Range("StartPremium")
rPremium(i, 1).Value = i
rPremium(i, 2).Value = colPrem(1).getGroupNumber

For Each myobjPremium In colPrem
c = DateDiff("m", myobjPremium.getEarnedDate, Range("reportdate").Value)
rPremium(i, 3 + c).Value = myobjPremium.getEarnedCollectedPremium
Next
End If
End Sub

Sub UpdateClaims(MyClaims As Collection, i As Long)
Dim myobjClaims As Claims
Dim rClaims As Range
Set rClaims = Range("StartIncurredPaidClaims")
Dim c As Long
Dim d As Boolean
If MyClaims.Count > 0 Then
rClaims(i, 1).Value = i
d = False

For Each myobjClaims In MyClaims
If myobjClaims.getIncurredDate > 0 Then
c = DateDiff("m", myobjClaims.getIncurredDate, Range("reportdate").Value)
rClaims(i, 3 + c).Value = myobjClaims.getIncurredPaidClaims
If d = False Then
rClaims(i, 2).Value = myobjClaims.getGroupNumber
d = True
End If
End If
Next
End If
End Sub

Sub UpdateMisc(colMisc As Collection, i As Long)
Dim myobjCaseChar As CaseChar
Dim rMembers As Range
Dim rEmployees As Range
Dim rRTNB As Range
Dim rLastIncrease As Range

Dim dDate As Date

Dim c As Long
Set rMembers = Range("StartMembers")
Set rEmployees = Range("StartEEs")
Set rRTNB = Range("StartRTNB")
Set rLastIncrease = Range("StartLastIncrease")
If colMisc.Count > 0 Then
rMembers(i, 1).Value = i
rMembers(i, 2).Value = colMisc(1).getGroupNumber
rEmployees(i, 1).Value = i
rEmployees(i, 2).Value = colMisc(1).getGroupNumber
rRTNB(i, 1).Value = i
rRTNB(i, 2).Value = colMisc(1).getGroupNumber
rLastIncrease(i, 1).Value = i
rLastIncrease(i, 2).Value = colMisc(1).getGroupNumber

For Each myobjCaseChar In colMisc
c = DateDiff("m", myobjCaseChar.getReportDate, Range("reportdate").Value)
rMembers(i, 3 + c).Value = myobjCaseChar.getMembers
rEmployees(i, 3 + c).Value = myobjCaseChar.getTotalEEs
rRTNB(i, 3 + c).Value = myobjCaseChar.getRTNB
rLastIncrease(i, 3 + c).Value = myobjCaseChar.getLastIncrease
Next
End If
End Sub

mdmackillop
07-05-2008, 01:41 PM
Hi Vedant,
Welcome to VBAX.

Can you post a workbook with sample data? Use Manage Attachments in the Go Advanced reply section
Regards
MD

Aussiebear
07-06-2008, 02:03 AM
Is there a blank cell within the collection (colRenewalCaseChar.Count) being counted?