Missing Number in a Loop of VBA generates error

Question

I have the following code which searches for the different available products using the sequence of numbers.

If one product number is missing this code throws an error message.

Example of my code:-

Sub mycode()

    icnt1 = 0
    max1 = Range("N1").Value

    For store = 1 To max1
        icnt1 = icnt1 + 1

        Windows(File6).Activate
        ActiveSheet.Range("$A$1:$K$10000").AutoFilter Field:=1, Criteria1:=icnt1

        Range("H2", Range("H" & Rows.Count).End(xlUp)).Select
        Selection.Copy

        Sheets("template").Copy Before:=Sheets("template")
        ActiveSheet.Name = "s" & icnt1

        Sheets("s" & icnt1).Select
        Range("T3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Next

End Sub

If I have a product list in accurate sequence (1, 2, 3, 4, 5 etc.) then I am not facing any issue.

If one or more products is missing (like 1, 2, 4, 5, 6 etc.) I am getting an error message. I tried to put an option of On Error Resume Next but that is also not working.

It is really unclear what the objective of your code is, and what it actually does. — Luuklag, Oct 17 '18 at 09:20
Getting an error message is not a good description of your problem. Please include what error, and where you get it. — Luuklag, Oct 17 '18 at 09:20
Thanks for your message, My code is trying to copy sales of each product (Column H of File6) and pasting it to Column T of a newly added sheet s1, s2 etc. I get error message when it searches for item 3 and which is not available. — Alok Nagar, Oct 17 '18 at 09:25
Not actually answering the question, but why do you have both `icnt1` and `store` if they are going to be *exactly the same value*? Why do re-select the already-`ActiveSheet` after you rename it? And, have a read on [how to avoid using Select in Excel](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1)... (e.g. `ActiveSheet.Range("T3").PasteSpecial`) — Chronocidal, Oct 17 '18 at 10:05

score 1 · Answer 1 · answered Oct 17 '18 at 09:25

You need to check first whether the filter returns anything. Assign it to a range and check it's not Nothing before proceeding.

Please also read up on (1) using Option Explicit and declaring your variables and (2) avoiding Select.

Sub mycode()

Dim r As Range

icnt1 = 0
max1 = Range("N1").Value

For store = 1 To max1
    icnt1 = icnt1 + 1
    Windows(File6).Activate
    With ActiveSheet
        .AutoFilterMode = False
        .Range("$A$1:$K$10000").AutoFilter Field:=1, Criteria1:=icnt1
        With .AutoFilter.Range
            On Error Resume Next
            Set r = .Offset(1, 7).Resize(.rows.count-1,1).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not r Is Nothing Then
                Sheets("template").Copy Before:=Sheets("template")
                ActiveSheet.Name = "s" & icnt1
                r.Copy
                Sheets("s" & icnt1).Range("T3").PasteSpecial Paste:=xlPasteValues
            End If
        End With
    End With
Next

End Sub

score 0 · Answer 2 · answered Oct 17 '18 at 09:26

You need to check if there are visible cells in the filter range before proceeding with the Copy/Add/Paste.

Sub mycode()

    dim icnt1  as long, max1 as long, store as long
    icnt1 = 0
    max1 = Range("N1").Value
    For store = 1 To max1
        icnt1 = icnt1 + 1

        with Windows(File6)
            if .autofiltermode then .autofiltermode = false
            .Range("$A$1:$K$10000").AutoFilter Field:=1, Criteria1:=icnt1
            with .resize(.rows.count-1, 1).offset(1, 7)
                if cbool(application.subtotal(103, .cell)) then
                    .Copy

                     Sheets("template").Copy Before:=Sheets("template")
                     ActiveSheet.Name = "s" & icnt1
                     Sheets("s" & icnt1).Select

                     Range("T3").PasteSpecial Paste:=xlPasteValues
                 end if
             end with
         end with

    Next store

End Sub

Hmm... Would it be faster/more efficient to use `WorksheetFunction.CountIf` to check for `icnt1` in Column A before filtering? — Chronocidal, Oct 17 '18 at 10:09
For a specific single filter value, yes but this is more universal. — , Oct 17 '18 at 10:12

Missing Number in a Loop of VBA generates error

2 Answers2