Application-Defined or Object-Defined Error while using Excel VBA

Question

I get "Application-Defined or Object-Defined Error" while I run the below Macro.

I want to parse through each column in "DB" sheets and search for that column.

Sub test()  
Dim FindString As Range
Dim Rng As Range

Dim i, j As Integer
Dim finalcol As Long

Worksheets("DB").Select

finalcol = Worksheets("DB").Cells(1, Application.Columns.Count).End(x1toleft).column
On Error Resume Next

For i = 1 To finalcol
    FindString = Cells(1, i).Value

    If Trim(FindString) <> "" Then
        With Sheets("DB").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
Next i
On Error GoTo 0

End Sub

Dick Kusleika · Accepted Answer · 2012-12-05 15:43:02Z

4

Your x1toleft constant should be xlToLeft (ex ell, not ex one). The fact that it doesn't convert to camel case is a tip-off.

Also, FindString should be Dim FindString As String not as Range. If you get rid of the On Error Resume Next line, you'll get an error on FindString = Cells(1,i).Value line because you have to use Set with object variables. When it runs and the error is suppressed, FindString (as a Range variable) is Nothing.

I didn't get the error you got, it just couldn't find anything. But if you make those changes, it will either fix it or expose the real error. In any case, you should remove error handling until you have it debugged, then add it back.

answered Dec 5, 2012 at 15:43

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Application-Defined or Object-Defined Error while using Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related