Consulting

Results 1 to 2 of 2

Thread: Object variable or With block variable not set error

  1. #1

    Object variable or With block variable not set error

    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.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,876
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •