title: Excel VBA "Method Range of object _worksheet failed
body: <p>I am trying to do some VBA programming but running into a strange situation. The code below runs fine in one excel file but not another, the error in the title appears in the line where I assign <code>SearchRange</code> variable.</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address = "$B$3" Then
          Dim rngX As Range
          Dim counter As Integer
          Dim wsLeit As Excel.Worksheet
          Dim wsFilt As Excel.Worksheet
          Dim searchValue As String
          Dim searchRange As Range
          Dim rownr As String
          Set wsLeit = Excel.ThisWorkbook.Sheets("Leit")
          Set wsFilt = Excel.ThisWorkbook.Sheets("FILT")
          Set searchRange = wsFilt.Range("A4:ZZ10000")
          searchValue = wsLeit.Range("B3").Value
          Set rngX = searchRange.Find(what:=searchValue, lookat:=xlPart)
          wsLeit.Range("A6:B200").ClearContents
          If Not rngX Is Nothing Then
              strFirstAddress = rngX.Address
              counter = 8
              rownr = Split(rngX.Address, "$")(2)
              For Each c In wsFilt.Range("A" &amp; rownr &amp; ":" &amp; "ZZ" &amp; rownr)
                  If Not IsEmpty(c.Value) Then
                      wsLeit.Range("A" &amp; CStr(counter)).Value = c.Value
                      foundColumn = Split(c.Address, "$")(1)
                      wsLeit.Range("B" &amp; CStr(counter)).Value = wsFilt.Range(foundColumn &amp; "1").Value &amp; " " &amp; wsFilt.Range(foundColumn &amp; "2").Value
                      counter = counter + 1
                  End If
              Next
          Else
              MsgBox "Fann ekkert"
          End If
      End If
  End Sub
  </code></pre>
  
  <p>Any idea why this code works in one workbook but not another? (the sheets have the same name in both books)</p>
  
  <p>EDIT1:</p>
  
  <p>For completeness sake here is the full error:</p>
  
  <pre><code>Runtime Error '1004':
  
  Method 'Range' of object '_Worksheet' failed
  </code></pre>