.Find method on filtered range

[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

 

[Joel]

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

 

[Kurt Biesemans]

Joel,

Thank you to rewrite my code to simplify the problem.

Kurt