CODEWITHSUNDEEP
vbaexcel
StormsEdge
StormsEdge

Reputation: 885

Error 91 occurring during iterations randomly

Interesting problem here. This line of code works through multiple iterations until it reaches a point where it throws an Run-time error 91 at me: "Object Variable or With block variable not set". This is occurring in a function designed to find a deal number. The entire program is an end of day email generation program that sends attachments to various different counter-parties. The error occurs on the ** line. For additional color, temp deal is not empty when execution is attempted. There doesn't appear to be any extraneous trailing or leading spaces either. Thanks in advance!

    Function getPDFs(cFirm As Variant, iFirm As Variant, row_counter As Variant, reportsByFirm As Worksheet, trMaster As Worksheet, trSeparate As Variant, trName As Variant, reportDate As Variant) As String

    dealCol = 1
    Dim locationArray() As String
    Dim DealArray() As String
    cDes = "_vs._NY"
    iDes = "_vs._IC"
    filePath = "X:\Office\Confirm Drop File\"
    dealNum = reportsByFirm.Cells(row_counter, dealCol)
    FileType = ".pdf"

    If InStr(1, dealNum, "-") > 0 Then

        DealArray() = Split(dealNum, "-")
        tempDeal = DealArray(LBound(DealArray))

    Else
        tempDeal = dealNum

    End If

    'Finds deal location in spread sheet for further detail to obtain file path
    **trLocation = trMaster.Columns(2).Find(What:=tempDeal).Address
    locationArray() = Split(trLocation, "$")
    trRow = locationArray(UBound(locationArray))

    'Formats client names for 20 characters and removes punctuation (".") in order to stay within convention of file naming
    cFirmFormatted = Trim(Left(cFirm, 20))
    iFirmFormatted = Trim(Left(iFirm, 20))

    'Finds clearing method
    clMethod = trMaster.Cells(trRow, 6).Value

    Select Case clmethod
        Case "Clport"

            'Prevents naming convention issues with punctuations in the name
            If InStr(1, cFirmFormatted, ".") > 0 Then
                cFirmFormatted = Replace(cFirmFormatted, ".", "")
            End If

            getPDFs = filePath & cFirmFormatted & "\" & reportDate & "_" & dealNum & "_" & cFirmFormatted & cDes & FileType

        Case "ICE"

            If InStr(1, iFirmFormatted, ".") > 0 Then
                iFirmFormatted = Replace(iFirmFormatted, ".", "")
            End If

            getPDFs = filePath & iFirmFormatted & "\" & reportDate & "_" & dealNum & "_" & iFirmFormatted & iDes & FileType
    End Select

End Function

Upvotes: 1

Views: 118

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Your code assumes that trLocation is always found, if it isn't found then you will receive an error because you don't have a range to return the .Address property for.

Try testing the result first:

Dim testLocation As Excel.Range

Set testLocation = trMaster.Columns(2).Find(tempDeal)

If Not testLocation Is Nothing Then
    trLocation = testLocation.Address
    '// Rest of code here...
Else
    MsgBox "Cannot find """ & tempDeal & """!"
    Exit Function
End If

Upvotes: 3

Related Questions