VBA: Checking a number in an array and proceeding when not present

Question

I am fairly new to VBA. In the below code, I am attempting to search the list of numbers in the array in a dynamic column. If the number is found, then a new line is inserted. However, I get an error when the number in the array is not found. If there is a more efficient way to do the same thing, I would be happy to change the code. Any help is greatly appreciated.

Sub InsertLines()

Dim vBUsort As Variant
Dim vSheet As Worksheet
Dim i As Integer
vBUsort = Array("3", "4", "5", "6", "7", "8", "9", "10")

    For Each vSheet In ThisWorkbook.Worksheets
        Range("A1").Select
        Application.ActiveSheet.Range("1:2").Find(What:="BU Sort Priority", _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False).Select

        With ActiveCell.EntireColumn.Select

        For i = LBound(vBUsort) To UBound(vBUsort)

            Selection.Find(What:=vBUsort(i), After:=ActiveCell, _
                LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False).Activate
                ActiveCell.EntireRow.Insert
                ActiveCell.EntireRow.Interior.Color = 6697728

        Next i
        End With
    Next vSheet

End Sub

score 1 · Accepted Answer · edited May 23 '17 at 11:51

You need to capture the result of the .Find() then test to see if you found anything:

Sub InsertLines()

Dim vBUsort As Variant
Dim vSheet As Worksheet
Dim i As Integer
Dim rng1 as Range
Dim rng2 as Range
  vBUsort = Array("3", "4", "5", "6", "7", "8", "9", "10")
  For Each vSheet In ThisWorkbook.Worksheets
        Range("A1").Select
        Set rng1 = Application.ActiveSheet.Range("1:2").Find(What:="BU Sort Priority", _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False).Select
        If not Rng1 = Nothing Then 
          With ActiveCell.EntireColumn.Select
            For i = LBound(vBUsort) To UBound(vBUsort)
              Set rng2 = Selection.Find(What:=vBUsort(i), After:=ActiveCell, _
                LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False)
                if not rng2 = Nothing then
                  rng2.select
                    ActiveCell.EntireRow.Insert
                    ActiveCell.EntireRow.Interior.Color = 6697728
                End If
            Next i
          End With
        End If
  Next vSheet

End Sub

Also, How to avoid using Select in Excel VBA macros

You're welcome. You may want to use a more descriptive variable name than `rng1` and `rng2`. I wasn't feeling particularly creative yesterday, so I went with the simple. You know your data, so give it a meaningful name to make your future life (or the life of the next maintainer) a bit easier. — FreeMan, Jun 11 '15 at 13:00

VBA: Checking a number in an array and proceeding when not present

1 Answers1