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.Co unt - 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

"Kurt Biesemans" wrote:

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.Co unt - 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,

Thank you to rewrite my code to simplify the problem.

Kurt


"Joel" wrote in message
...
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" wrote:

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.Co unt - 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