CODEWITHSUNDEEP
vbaexcel
user3221162
user3221162

Reputation: 127

Run-time error '1004' Application-definded or object-defined error. VBA EXCEL

here's what my macro does. It finds a string given a large excel file and goes to that column. At that point, it finds a user inputted string and copies all results onto the column next to it. I started learning VBA yesterday, so any help is appreciated.

here's where it gets the error While InStr(UCase(Worksheets("Sheet1").Cells(1, j)), UCase("request")) = 0

Here's my complete macro so far.

    Sub FineMe()

Dim i, j As Long
Dim count, test As Integer

userinput = InputBox("Enter String", Search, "Collect user input")
Cells.Interior.ColorIndex = 28

 While InStr(UCase(Worksheets("Sheet1").Cells(1, j)), UCase("request")) = 0
    j = j + 1

    Wend

EndRow = Worksheets("Sheet1").Cells(Rows.count, j).End(xlUp).Row
count = 1
    For i = 1 To EndRow
        test = InStr(UCase(Cells(i, j)), UCase(userinput))
            If test > 0 Then
            Cells(count, j + 1).Value = Cells(i, j).Value

            count = count + 1

            End If
    Next i

End Sub

Any help would be appreciated. THANKS!

Upvotes: 1

Views: 643

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

i think Instr(1, what you wrote. clic Instr in VB editor, and press F1

Upvotes: 0

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Just add j = 1 before While, because after declaration Dim i, j As Long we have j equals to 0 and Worksheets("Sheet1").Cells(1, 0) triggers an error (we haven't Cells(1,0))

Sub FineMe()

    Dim i, j As Long
    Dim count, test As Integer

    userinput = InputBox("Enter String", Search, "Collect user input")
    Cells.Interior.ColorIndex = 28

    j = 1
    While InStr(UCase(Worksheets("Sheet1").Cells(1, j)), UCase("request")) = 0
        j = j + 1
    Wend

    EndRow = Worksheets("Sheet1").Cells(Rows.count, j).End(xlUp).Row
    count = 1
    For i = 1 To EndRow
        test = InStr(UCase(Cells(i, j)), UCase(userinput))
            If test > 0 Then
            Cells(count, j + 1).Value = Cells(i, j).Value

            count = count + 1

            End If
    Next i

End Sub

BTW, in line Dim i, j As Long only j is Long, but i is Variant. You should use Dim i As Long, j As Long instead. The same thing with Dim count, test As Integer - you should declare it as follows: Dim count As Integer, test As Integer

Upvotes: 1

Related Questions