CODEWITHSUNDEEP
vbaexcelloopsobjectfind
Coding Novice
Coding Novice

Reputation: 447

Object Variable or With Block Variable Not set in loop using find function

Sub Main()
Dim FName As Variant, R As Long, DirLoc As String, i As Integer
R = 1
i = 1
DirLoc = ThisWorkbook.Path & "\" 'location of files
FName = Dir(DirLoc & "*.csv")
Do While FName <> ""
    ImportCsvFile DirLoc & FName, ActiveSheet.Cells(R, 1)
    R = ActiveSheet.UsedRange.Rows.Count + 1
    FName = Dir
    For i = 1 To 100
        Worksheets("RAW").Range("B1:B6").Copy
        Worksheets("filtered").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial , Transpose:=True
        Cells.Find(What:="Run:", After:=Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Select
        Worksheets("filtered").Cells(10, 10).Value = i
        If ActiveCell <> "Run:" & i Then
            Exit For
        End If
    Next i
    DeleteFiltered
Loop
End Sub

I am having trouble as I am getting an error at this: 

           `Cells.Find(What:="Run:" & i, After:=Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Select`

The error does not occur when I remove "& i". I use this to import a data file, and then find a specific "Run:1." It should then copy the data and find the next Run, and the one after that. This is why I need the & i. How can I make this work?

There are some more code sections that are definitely okay, so I left them out.

Upvotes: 1

Views: 1219

Answers (1)

ChipsLetten
ChipsLetten

Reputation: 2953

If the Cells.Find(What:="Run:" & i,... fails to find a match, the Select part of the statement will then cause the error. You should always store the result of a Find in a range variable and then test that for Nothing.

Add this declaration to your code:

Dim cellsFound As Range

And replace this:

    Cells.Find(What:="Run:", After:=Cells(1, 1), _
        LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
    Worksheets("filtered").Cells(10, 10).Value = i
    If ActiveCell <> "Run:" & i Then
        Exit For
    End If

With:

Set cellsFound = Worksheet("sheet_name").Cells.Find(What:="Run:" & i, After:=Worksheet("sheet_name").Cells(1, 1), _
        LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
If Not(cellsFound Is Nothing) Then
    Worksheets("filtered").Cells(10, 10).Value = i
Else
    ' not found
    Exit For
End If

Upvotes: 1

Related Questions