PDA

View Full Version : Object variable or With block variable not set error



next
09-24-2013, 08:24 AM
This error occurs when I search a spreadsheet for nonexistent value. This is so strange, how do I fix it!?
Here's my code:

Sub test()
Dim xmlSheet As Worksheet
Dim pvLoc As Variant
Set xmlSheet = ActiveWorkbook.Worksheets("Sheet1")
pvLoc = ""

pvLoc = xmlSheet.Cells.Find("111123").Address(False, False)
If ((pvLoc = "") Or (UCase(Range(pvLoc).Offset(0, 8).value) = "PHONE")) Then Debug.Print "failed"
End Sub

Error is generated by this line: pvLoc = xmlSheet.Cells.Find("111123").Address(False, False)
If the find method is changed to something that is on the spreadsheet, then everything works ok.

p45cal
09-24-2013, 10:13 AM
try:
Sub test()
Dim xmlSheet As Worksheet
Dim pvLoc As Range
Set xmlSheet = ActiveWorkbook.Worksheets("Sheet1")
'Set pvLoc = Nothing 'only needed if within a loop.

Set pvLoc = xmlSheet.Cells.Find("111123")
If pvLoc Is Nothing Then
Debug.Print "failed"
Else
If pvLoc.Offset(0, 8).Value = "PHONE" Then
Debug.Print "failed"
Else
Debug.Print "success"
End If
End If
End Sub