CODEWITHSUNDEEP
vbaexcel
Ollie
Ollie

Reputation: 209

Loop through a list with an autofilter

I have a list of companies (listA, located in worksheet Workings2) and a list of companies(ListB - each company has multiple entries, located in worksheets data) with data attached, for list A i want to be able to take a company (in a cell in column B) and loop through listB on an Auto filter so that I can check if a certain column (AZ) has a certain count of values in a particular band (100,000-500,000). once I have the count of numbers I want to add it to a worksheet workings. After each company in ListB, I want to reset the filter and aplly the filter for the next company

I have the code below, which i have scrambled together, but it gives out a wacky number that makes no sense (in the order of 30,000, when I'm looking at around 100)

Sub calculate1()

Dim wsCriteria As Worksheet, wsData As Worksheet, wsDest As Worksheet
Dim CompanyListLocation
Dim lr As Long, dlr As Long
Application.ScreenUpdating = False
Set wsCriteria = Sheets("Dashboard")
Set wsData = Sheets("Data")
Set wsDest2 = Sheets("Workings2")

Dim rCell As Range
Dim rRng As Range
Set rRng = wsData.Range("B3: B418")

For i = 0 To 419

   With wsData.Rows(1)
        .AutoFilter Field:=5, Criteria1:=i

lr = wsData.UsedRange.Rows.Count
dlr = wsDest2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'wsData.AutoFilterMode = False

With wsData.Rows(1)
.AutoFilter Field:=3, Criteria1:="Forward", Operator:=xlOr, Criteria2:="NDF"
  If wsDest2.Range("C1:C" & lr).SpecialCells(xlCellTypeVisible).Cells.Count 
> 1 Then
  ff = Application.WorksheetFunction.CountIfs(wsData.Range("AZ:AZ"), ">" & 
100000, wsData.Range("AZ:AZ"), "<" & 500000)
wsDest2.Range("C3").Value = ff
     End If
.AutoFilter
End With
End With

Next i
End Sub

It would be amazing if anyone could help out

Upvotes: 0

Views: 219

Answers (1)

user4039065
user4039065

Reputation:

I'm still unclear on why this is not what you want to do. It seems a much more succinct solution. You need to add the company filter (e.g. .AutoFilter Field:=5, Criteria1:=i) into the COUNTIFS to get the correct result. COUNTIFS is not filtered by .AutoFilter.

=SUM(COUNTIFS(E:E, BB2, AZ:AZ, ">="&BC2, AZ:AZ, "<="&BD2, C:C, {"forward","ndf"}))

enter image description here

Upvotes: 1

Related Questions