CODEWITHSUNDEEP
vbaexcel
ojs
ojs

Reputation: 502

Excel VBA "Method Range of object _worksheet failed

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 SearchRange variable.

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" & rownr & ":" & "ZZ" & rownr)
                If Not IsEmpty(c.Value) Then
                    wsLeit.Range("A" & CStr(counter)).Value = c.Value
                    foundColumn = Split(c.Address, "$")(1)
                    wsLeit.Range("B" & CStr(counter)).Value = wsFilt.Range(foundColumn & "1").Value & " " & wsFilt.Range(foundColumn & "2").Value
                    counter = counter + 1
                End If
            Next
        Else
            MsgBox "Fann ekkert"
        End If
    End If
End Sub

Any idea why this code works in one workbook but not another? (the sheets have the same name in both books)

EDIT1:

For completeness sake here is the full error:

Runtime Error '1004':

Method 'Range' of object '_Worksheet' failed

Upvotes: 2

Views: 1158

Answers (1)

Ioannis
Ioannis

Reputation: 5408

The problem was that pre-Excel 2007 versions have a smaller number of columns, and this sometimes creates random failures when the file format changes.

Some Excel 2003 specs:

Worksheet size 65,536 rows by 256 columns Column width 255 characters Row height 409 points Page breaks 1000 horizontal and vertical

Excel 2010:

Worksheet size 1,048,576 rows by 16,384 columns Column width 255 characters Row height 409 points Page breaks 1,026 horizontal and vertical Total number of characters that a cell can contain 32,767 characters

A good practice to safeguard against this problem is to add a "guard" subroutine in the Workbook_Open event that checks (using Application.Version) if the current excel version is the minimum one for which the file is meant to be. You can store the minimum excel version in the Workbook.Names Collection as a constant and check against that constant.

I hope this helps!

Upvotes: 3

Related Questions