VBA debug issue

Question

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

markblandford · Accepted Answer · 2012-02-27 15:51:52Z

4

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

answered Feb 27, 2012 at 15:51

markblandford

3,2133 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA debug issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related