CODEWITHSUNDEEP
excelvbaforeachvisible
flanigaa
flanigaa

Reputation: 15

For Each loop on filtered data returning 0 results, no errors

I need to generate a sheet of values out of a database between dates that the user selects. The date is in column 2 of the database, but I need the whole row for every date in this range. I got some advice to use a For Each instead to more easily use the SpecialCells(xlCellTypeVisible). While I am no longer getting any errors I also get no data in my product worksheet. Could someone tell me why I am not returning data?

Sub Generate()

Dim g As Integer
Dim h As Integer
Dim datemin As String
Dim datemax As String

datemin = CDbl(CDate(Sheets("start").Cells(15, 8)))
datemax = CDbl(CDate(Sheets("start").Cells(15, 9)))


Worksheets("Database").Range("A1").AutoFilter Field:=10, Criteria1:=">=" & datemin, _
        Operator:=xlAnd, Criteria2:="<=" & datemax


g = 0

For Each Row In Worksheets("database").Range("A1")
    g = g + 1
    If Cells(g, 1).SpecialCells(xlCellTypeVisible) = True And Cells(g, 1) <> "" Then

Sheets("product").Activate
Dim NextRow As Long
NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 10

'fill KPI
Cells(NextRow, 1) = Format(Sheets("database").Cells(g, 1), "mm/dd/yyyy")  'Date1
Cells(NextRow, 2) = Format(Sheets("database").Cells(g, 2), "mm/dd/yyyy")  'Date2
Cells(NextRow, 3) = Sheets("database").Cells(g, 3)  'value1
Cells(NextRow, 4) = Sheets("database").Cells(g, 4)  'value2
Cells(NextRow, 6) = Sheets("database").Cells(g, 5)  'value3
Cells(NextRow, 9) = Sheets("database").Cells(g, 8)  'comment
Cells(NextRow, 13) = Sheets("database").Cells(g, 6)  'person
    Else
        Exit For
    End If

Next

End Sub

Upvotes: 1

Views: 67

Answers (1)

norie
norie

Reputation: 9857

You are only 'looping' through one cell - A1.

If you want to use a loop for this try looping through all the rows on the database and checking if they are visible or not.

If they are visible then copy the relevant data to the other sheet.

Sub Generate()
Dim rngDst As Range
Dim rngSrc As Range
Dim datemin As String
Dim datemax As String
Dim g As Integer
Dim h As Integer

    datemin = CDbl(CDate(Sheets("start").Cells(15, 8)))
    datemax = CDbl(CDate(Sheets("start").Cells(15, 9)))


    Worksheets("Database").Range("A1").AutoFilter Field:=10, Criteria1:=">=" & datemin, _
                                                      Operator:=xlAnd, Criteria2:="<=" & datemax

    Set rngSrc = Worksheets("Database").Range("A2")
    Set rngDst = Worksheets("Product").Range("A11")
    
    Do
    
        
        If Not rngSrc.EntireRow.Hidden And rngSrc.Value <> "" Then

            'fill KPI
            rngDst.Value = Format(rngSrc.Value, "mm/dd/yyyy")  'Date1
            rngDst.Offset(, 1).Value = Format(rngSrc.Offset(, 1).Value, "mm/dd/yyyy") 'Date2
            rngDst.Offset(, 2).Value = rngSrc.Offset(, 2).Value 'value1
            rngDst.Offset(, 3).Value = rngSrc.Offset(, 3).Value 'value2
            rngDst.Offset(, 5).Value = rngSrc.Offset(, 4).Value 'value3
            rngDst.Offset(, 8).Value = rngSrc.Offset(, 7).Value 'comment
            rngDst.Offset(, 12).Value = rngSrc.Offset(, 5).Value 'person
            Set rngDst = rngDst.Offset(1, 0)
        End If
        
        Set rngSrc = rngSrc.Offset(1, 0)
    Loop Until rngSrc = ""

End Sub

Upvotes: 1

Related Questions