VBA To Capture Values From Cells

Hi,

You could do this

Function GetInfo()
Dim lookupVal As String, TotalSales As Double, PendingSaleAmt As Double
Dim cell As Range
lookupVal = "Blue"

With Sheets("Data")
    With .ListObjects("DataInfo").Range
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=lookupVal
        .AutoFilter Field:=5, Criteria1:=">=01/01/2017", Operator:=xlAnd, Criteria2:="<=02/01/2017"
        TotalSales = Application.WorksheetFunction.Subtotal(103, .Columns(3)) - 1
        For Each cell In Sheets("Data").Range("D:D").SpecialCells(xlCellTypeVisible).Cells
            If IsNumeric(cell.Value2) And cell.Value2 <> 0 Then
                PendingSaleAmt = cell.Value2
                Exit For
            End If
        Next cell
        'Need this to print 3,400
        Debug.Print TotalSales
        'Need this to print first non null or 0 value
        'Need this to print 100
        Debug.Print PendingSaleAmt
    End With
End With

End Function

@xlnitwit:

I was coming up with a similar solution, but just FYI, you don't have to do the IsNumeric check, you can just do a specialcells subset of a specialcells set. Given a large enough data set, the speed up would be noticeable.

For Each cell In Sheets("Data").Range("D:D").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, xlNumbers)
If cell.Value2 <> 0 Then

@walruseggman,

Great name!

That is true, although
1. You need to break that into two lines, because if the first specialcells method only returned one cell, the second would apply to the entire sheet.
2. If I had a really large dataset I probably wouldn't use an autofilter at all.

Actually, scrap point 1. It seems that doesn't apply if you chain specialcells commands. It would still need an error handler though.