CODEWITHSUNDEEP
vbaexcel
Eem Jee
Eem Jee

Reputation: 1319

Run Time Error 91 in Excel VBA

I have this function to check blank and non-blank cell in VBA.

Function toCheckBlanks(rng1 As String, rng2 As Range)
Dim iBlank&, iNonBlank& '& declare variables as long
Set main = ThisWorkbook.Sheets("Main")
Dim rng As Range
Set rng = Nothing

Set rng = main.Range(rng1 & [rng2].Find("*", , , , , xlPrevious).Row)

With WorksheetFunction
    iNonBlank = .CountA(rng) 'count non-blank
    iBlank = .CountBlank(rng) ' count blank
End With

If iBlank > 0 Then
    toCheckBlanks = True
End If

Set rng = Nothing
End Function

I've tried to use it this way:

If toCheckBlanks("O23:O", Range("O23:O32")) Then exit sub

This line returns an error:

Object variable or With block Variable not set

Set rng = main.Range(rng1 & [rng2].Find("*", , , , , xlPrevious).Row)

But sometimes if I run it, it does not have an error, and other times it errors. Any heads up?

Upvotes: 0

Views: 55

Answers (3)

user3598756
user3598756

Reputation: 29421

I think you can shorten it down to:

Function toCheckBlanks(rng1 As String, rng2 As Range) As Boolean
    Dim rng As Range

    Set rng = rng2.Find("*", , , , , xlPrevious)
    If Not rng Is Nothing Then
        Set rng = ThisWorkbook.Sheets("Main").Range(rng1 & rng2.Find("*", , , , , xlPrevious).Row)
        toCheckBlanks = WorksheetFunction.CountBlank(rng) > 0
    End If
End Function

Upvotes: 0

YowE3K
YowE3K

Reputation: 23994

Try this:

Function toCheckBlanks(rng1 As String, rng2 As Range) As Boolean
    Dim iBlank&, iNonBlank& '& declare variables as long
    Dim FindResult As Range
    Set main = ThisWorkbook.Sheets("Sheet1")
    Dim rng As Range

    Set FindResult = rng2.Find("*", , , , , xlPrevious)
    If FindResult Is Nothing Then
        'do whatever is appropriate if rng2 is Empty
        '...
        toCheckBlanks = False
    Else
        Set rng = main.Range(rng1 & FindResult.Row)

        With WorksheetFunction
            iNonBlank = .CountA(rng) 'count non-blank
            iBlank = .CountBlank(rng) ' count blank
        End With

        toCheckBlanks = iBlank > 0

    End If
End Function

Upvotes: 1

lllpratll
lllpratll

Reputation: 378

your function syntax is not so good

firstly you should specify the return data type of the function i.e.

Function toCheckBlanks(rng1 As String, rng2 As Range) as Boolean

also you only set the value of toCheckBlanks (the value being returned by the function) in your if statement on the condition (iBlank > 0). therefore if iBlank is <= 0 your function will not work properly. You need to ensure toCheckBlanks is always set, usually this is done by initialising it to a default value at the start of the function

looks like there is a bunch of noise and unnecessary lines in your code, it needs a bit of a tidy up

Upvotes: 0

Related Questions