up vote 1 down vote favorite

I get "Application-Defined or Object-Defined Error" while I run the below Macro. currently, in this code, I wanted 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

share|improve this question

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
You're an eagle eye! – Doug Glancy Dec 5 '12 at 17:15
@Dick Kusleika, Thanks a lot. Its working :) – tester Dec 6 '12 at 6:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.