CODEWITHSUNDEEP
excelvbaexcel-2007
PlatiNUM
PlatiNUM

Reputation: 99

VBA debug issue

This macro runs on the click of a button. I receive an error.

Run-time error '91': Object variable or With block variable not set

I click Debug and it leads me to this highlighted area.

Selection.Find(What:=DateString, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate

Here is the entire function

Function GetBalance(Month As Integer) As Currency
'This function is called by the Calculate_Balance subroutine. It
'finds the appropriate month's balance on an employee sheet and sends
'it back to the calling routine.

Dim DateString As String
Dim RangeString As String
Dim Balance
Dim BalDate As Date
Dim strCurrMonth As String
Dim strCurrYear As String
Dim strFirstDayCurrMonth As String

    strCurrMonth = CStr(DatePart("m", Date))
    strCurrYear = CStr(DatePart("yyyy", Date))
    strFirstDayCurrMonth = strCurrMonth & "/1/" & strCurrYear
    dtmFirstDayCurrMonth = CDate(strFirstDayCurrMonth)

    DateString = Month & "/1/"

    Columns("A:A").Select
    Range("A6").Activate
    Selection.Find(What:=DateString, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate

    CurrRow = ActiveCell.Row
    BalanceRow = CurrRow - 1 'Move up 1 row to get last balance for this month

    RangeStr = "E" & BalanceRow
    DateRangeStr = "A" & BalanceRow

    BalDate = Range(DateRangeStr).Value
    If BalDate <= dtmFirstDayCurrMonth Then
        Balance = Range(RangeStr).Value
    Else
        Balance = 0
    End If

    GetBalance = Balance

End Function

Upvotes: 2

Views: 407

Answers (1)

markblandford
markblandford

Reputation: 3193

The Find() function returns a Range object so with your code if nothing is found, you will get a error as it can not 'activate' nothing. Change the code to something like:

Dim rng As Range

Set rng = Selection.Find(What:=DateString, After:=ActiveCell, LookIn:=xlFormulas, SearchDirection:=xlNext, MatchCase:=False)

If Not (rng Is Nothing) Then
    rng.Activate
End If

Upvotes: 4

Related Questions