CODEWITHSUNDEEP
vbaexcel
jz3
jz3

Reputation: 521

vba search function

I'm writing a function that find's a string in a worksheet, and sets a range variable with the location of the found phrase. When I try to check if the .find returned a value, I get an error:

Public Function ExceptionSearch(ByVal eString As String) As Boolean

Dim pRange As Range
Dim bDT As Boolean
Dim bSVR As Boolean
Dim bMB As Boolean

Workbooks("test.xlsx").Worksheets("worksheet").Activate

With Worksheets("worksheet").Range("a1:a5000")

Set pRange = .Find(eString, LookIn:=xlValues)

If pRange <> "" Then
bDT = True
Else
bDT = False
End If

End With


ExceptionSearch = bDT

End Function

I keep getting the error "Run-time error '91': Object variable or With block variable not set"

This is probably a simple problem, but I just can't figure it out.

Upvotes: 1

Views: 4906

Answers (2)

Paul McLain
Paul McLain

Reputation: 359

You should check to make sure pRange exists before checking the value against the blank string. 

Set pRange = .Find(eString, LookIn:=xlValues)

If Not pRange Is Nothing Then
    ' string check here 
End If

Upvotes: 2

jonsca
jonsca

Reputation: 10381

Since the .Find returns an object, it is equal to Nothing if the value is not found. Change it to:

If pRange Is Nothing Then
    bDT = True
Else
    bDT = False
End If

Or really, you could eliminate the if/else block altogether and just do ExceptionSearch=pRange Is Nothing since pRange Is Nothing will come back with a Boolean anyway

Upvotes: 1

Related Questions