cannot for the life of me get worksheetfunction.filter to work the same as as the excel formula. what am i missing

Varidian

New Member
Joined
Jan 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
HI all, I am really struggling to get the worksheetfunction.filter to work.

I have a formula in in a worksheet that filters (because i want to return multiple values) and it works in excel. if i use worksheet.formula in the vba it also works. however I
want to just return the values only, not as a spill. the formula for this is

1611947766986.png


however when I can get it working the worksheetfunction. i have tried all sorts, the last attempt i cant even get an array to populate. please can you offer some advice? or even a code example?

i really do appreciate your help.

below is the code.

i can post the data iff needed ( i have also tried xlookup to return all the values, but I cant get it to return anything other than a single entry)

VBA Code:
Sub ListServicetostaff()
    'Copies a list of all services from Home Sheet,.

    'Set Variables
    Dim StaffWs As Worksheet, HomesWs As Worksheet
    Dim HomesNamerng As Range, Staffnamerng As Range, StaffServnamerng As Range, Servarrrng As Range, Namearrrng As Range
    Dim Homeslr As Long, stafflr As Long, x As Long, prplr As Long
    Dim Namearr() As Variant, Servarr() As Variant, FilterArr As Variant, Filtered1() As Variant
   
   
    'Set Worksheet name Variables
    Set StaffWs = ThisWorkbook.Worksheets("Staff Details")
    Set HomesWs = ThisWorkbook.Worksheets("Homes")
     
    'Find Last Row of worksheets and Declare Variables
    stafflr = StaffWs.Range("A" & Rows.Count).End(xlUp).row
    Homeslr = HomesWs.Range("A" & Rows.Count).End(xlUp).row
   
    'Set Named Ranges
    Set Staffnamerng = StaffWs.Range("E2:E" & stafflr)
    Set HomesNamerng = HomesWs.Range("A2:B" & Homeslr)
       
'    Create Service to Staff sheet in this workbook with loop and delete sheet _
        sheet if it already exists prior to creating.
'        For Each Worksheet In ThisWorkbook.Worksheets
'            If Worksheet.Name = "Service To Staff" Then
''               Disable Alert
'                Application.DisplayAlerts = False
'                    Worksheet.Delete
''               Enable Alert
'                Application.DisplayAlerts = True
'            End If
'        Next Worksheet
'        ThisWorkbook.Worksheets.Add(After:=Sheets("License Calculator")).Name _
'            = "Service To Staff"
       
     'Declare sheet name as variable and set
     Dim ServiceToStaffws As Worksheet
     Set ServiceToStaffws = ThisWorkbook.Worksheets("Service to Staff")
   
    'Find last row of sheet
     ServiceToStafflr = ServiceToStaffws.Range("A" & Rows.Count).End(xlUp).row
    
       
    'Copy Service names from Homes Sheet and sort by Provider
     HomesNamerng.Copy Destination:=ServiceToStaffws.Range("A2")
     Range("A:B").Sort key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
    
      'Call Sub() that will Prep a sheet so that we can match each service _
            to every staff that has access to access care data for that service
'    Call PrepStaffForServiceMatch
   
    Dim PrepNameRng As Range, PrepServRng As Range
   
       'Create additional variables and array for filter
      
    Dim Prpedws As Worksheet
    Set Prpedws = ThisWorkbook.Worksheets("PrepedStaffToService")
        prplr = Prpedws.Range("A" & Rows.Count).End(xlUp).row
    Set PrepNameRng = Prpedws.Range("A1:A" & prplr)
    Set PrepServRng = Prpedws.Range("B1:B" & prplr)
    Set Namearrrng = Prpedws.Range("A1:A" & prplr)
    Set Servarrrng = ServiceToStaffws.Range("A1:a" & prplr)
 
   
        FilterArr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(PrepNameRng))
        'Servarr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(Servarrrng))
        'Loop to Match Staff to Service
       
        Application.ScreenUpdating = False
       
        On Error Resume Next
        For x = 2 To Homeslr
            Filtered1 = Application.WorksheetFunction.filter(FilterArr, Prpedws.Range("A" & x).Value)
             
        Next x
       
''     'Loop to Match Staff to Service
'     For x = 2 To Homeslr
'        ServiceToStaffws.Range("C" & x).Formula2R1C1 = _
'            "=TRANSPOSE(FILTER(PrepedStaffToService!C[-2],PrepedStaffToService!C[-1]='Service To Staff'!RC[-2]))"
'     Next x
'         'Copy Formulas and paste there values
'        ServiceToStaffws.UsedRange.Copy
'        ServiceToStaffws.Range("A1").PasteSpecial Paste:=xlPasteValues

      'Write Headers including dynamic Header for Staff Name
    ServiceToStaffws.Range("A1") = "Service Name"
    ServiceToStaffws.Range("B1") = "Provider Name"

    'Clean up sheets used for functions
'         Application.DisplayAlerts = False
'            ThisWorkbook.Worksheets("PrepedStaffToService").Delete
'         Application.DisplayAlerts = True
         
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,275
Office Version
  1. 365
Platform
  1. Windows
How is it not working?

Errors? Unexpected results?

Also, I notice in your code 'filter' is in lower case - have you named anything, e.g. a variable, 'filter'?
 

Varidian

New Member
Joined
Jan 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Norie, the code in its current format does not populate the Filter1 (Array) and therefor i am unable to get it to populate the cells "C" & x with the filtered results.

if I remove the on error resume next get error 1004 "Unable to get the filter property of the worksheet function.

to answer your question re: filter being in lower case, this was tabbed from intelisence.

I attach a sample of the work books.

- removed the personal data by smozgur -
 
Last edited by a moderator:

Varidian

New Member
Joined
Jan 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This is the Block giving the issue.
VBA Code:
   For x = 2 To Homeslr
            filtered1 = Application.WorksheetFunction.filter(FilterArr, Prpedws.Range("A" & x).Value)
             
        Next x
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,275
Office Version
  1. 365
Platform
  1. Windows
The 2nd argument of FILTER is an array of booleans, all you are passing is a single, text value.

What are you trying to return from the filter anyway?
 

Varidian

New Member
Joined
Jan 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The 2nd argument of FILTER is an array of booleans, all you are passing is a single, text value.

What are you trying to return from the filter anyway?

I have 390 properties and 430 staff, i want to list each member of staff that has access to a property, i thought the 3rd argument was a boolean, according to this article Filter function (Visual Basic for Applications) | Microsoft Docs also the formula in excel ( which works) is
1611957256559.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,799
Office Version
  1. 365
Platform
  1. Windows
That link is to the VBA Filter function, which is not the same as the Excel function.
Even if you code worked, you are overwriting the values in Filtered1 without doing anything with them.
 

Varidian

New Member
Joined
Jan 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
That link is to the VBA Filter function, which is not the same as the Excel function.
Even if you code worked, you are overwriting the values in Filtered1 without doing anything with them.
Hi Fluff,

the worksheetfunction i tried by converting the formula, or at least what i thought was right. which i why i turned to arrays. I cant find any documentation for the worksheetfunction over that using excel.. so i have reverted to the VBA function and alas I cant get that to work either. think i may stick to knitting :) (If i record the macro it just uses the function and spills the results.


im open to better ideas of doing this. Filtering just seemed like the most logical way.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,799
Office Version
  1. 365
Platform
  1. Windows
The VBA & Excel Filter functions are two completely beasts & do different things.
Why not just put the formula into the sheet?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,159
Messages
5,629,038
Members
416,362
Latest member
Cocito

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top