.Find method on filtered range

  • Thread starter Thread starter Kurt Biesemans
  • Start date Start date
K

Kurt Biesemans

Hi All,

I focus the next problem. Somewhere in code I return a filtered range in a function

Public Function GetHistoricalSalesForAccount(accountId As String) As Range
In this function there is an autofilter applied on some of the columns in the sheet:
Set filteredRange = historicalSheet.AutoFilter.Range.Offset(1, 0).Resize(historicalSheet.AutoFilter.Range.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
So I return the filteredRange in the function.

Now in my calling sub I have the following code:
Set historicalSales = GetHistoricalSalesForAccount", accountID)
Now the trick, I need to find a specific SKU in this 'filtered' list
Dim SKUHistoricalSalesSearchResult As Range
Set SKUHistoricalSalesSearchResult = historicalSales.Cells.Find(What:=sku, LookIn:=xlValues, LookAt:=xlWhole) 'Find SKU in historical sales

Now everytime the result is NOTHING although the sku is in the filtered list.

anybody any idea?

Regards
Kurt
 
Simplifiy your code to make it easier to isolate the problem and make the
code easier to understand

with historicalSheet
Set LastRow = .Range("A" & Rows.Count).end(xlup).Row
Set filteredRange = .Range("A2:A" & LastRow)
filteredRange.Autofilter
set AccountIDs = FilterRange.offset(0,1)
Set SKUHistoricalSalesSearchResult = AccountIDs.Cells.Find(What:=sku, _
LookIn:=xlValues, _
LookAt:=xlWhole)
end with
 
Back
Top