CODEWITHSUNDEEP
excelvba
eponkratova
eponkratova

Reputation: 477

The search block throws 'Run-time error 91'

Good day, When I search within a column for a particular string, I get the 91 run error.

I have tried to modify the logic - copied the result to the new sheet and then, did the deletion - see the second piece of code.

Then, I found that it happens because the vba cannot find the text, so I asked a person who runs this report in the requested language to change "??????? ATLAS ????-???" to the way it is written in the data source language. But it didn't help.

    Columns("A:A").Select
    Selection.Find("??????? ATLAS ????-???", LookIn:=xlValues).Select
    Range(ActiveCell.Address & ":" & Cells(Cells(Rows.Count, "A").End(xlUp).Row, ActiveCell.Column + 4).Address).Select
    Selection.Copy
    'Pasting the Ylan-Yde data to the new sheet
    Sheets("interim").Select
    Range("A1").Select
    ActiveSheet.Paste

    'Copying the Ylan-Yde data to a new sheet
    Cells.Select
    Selection.Copy
    Sheets("interim").Select
    Cells.Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Selection.Find("??????? ATLAS ????-???", LookIn:=xlValues).Select
    Range("A1:A" & ActiveCell.Row - 1).EntireRow.Delete```


If I stick to the 2nd version, the code is supposed to find the a certain string in a column (it is present only once) and delete all rows before the row with the found string.

Upvotes: 0

Views: 36

Answers (1)

Damian
Damian

Reputation: 5174

You should avoid using .Selectbecause everything can go wrong if the user interacts with excel. Instead you need to Declare variables for your workbooks and worksheets.

This approach is assuming your word only appears once and its Length is always 22, so the other ATLAS that shows on the column don't have the same length. With that in mind this should work:

Option Explicit
Sub Test()

    Dim arr As Variant, LastRow As Long, ws As Worksheet, wsInterim As Worksheet, i As Long, StartRow As Long, NowDelete As Boolean


    With ThisWorkbook
        Set ws = .Sheets("NameWhereAtlasIs")
        Set wsInterim = .Sheets("Interim")
    End With
    With ws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        arr = .Range("A1:A" & LastRow).Value 'store the column A inside an array
    End With

    NowDelete = False

    For i = 1 To UBound(arr) 'find the ATLAS with the exact length match.
        If arr(i, 1) Like "*ATLAS*" And Len(arr(i, 1)) = 22 Then
            StartRow = i
            NowDelete = True
            arr(i, 1) = vbNullString 'if you intend to delete this row too, the one with the ATLAS
        End If
        If NowDelete Then arr(i, 1) = vbNullString 'from the moment you find your match all the rows will be emptied on column A
    Next i

    ws.Range("A" & StartRow & ":A" & LastRow).Copy wsInterim.Range("A1")

    ws.Range("A1:A" & LastRow).Value = arr 'this would paste the array back with the empty cells you cleared before

End Sub

Upvotes: 1

Related Questions