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:

Varidian

New Member
Joined
Jan 27, 2021
Messages
11
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?
Speed, the first argument and first part of the second argument, prior to the equals need to perform it against 8500 rows, this then needs to be done 360 times, leaving a volitile spill. i did then perform a copy and paste, but I just though that the overall speed doing it through the worksheetfunction. would improve performance.

have you got any links regarding the documentation of the worksheetfuntion.filter?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,275
Office Version
  1. 365
Platform
  1. Windows
If you are going to use arrays why not simply loop?
 

Varidian

New Member
Joined
Jan 27, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
If you are going to use arrays why not simply loop?
It may come to that. I'm new to VBA and learning as I go. I haven't coded properly for about 10 years and even then it was sporadic. So i can use all the help I can get.

This is more of a personal growth hobby, which I'm using work as the basis of my learning.

how qould i go about wriing the loop to use a filter (match) and then put the returned value in ("C" & x). Thanks for your help, even if you are not able to help any further.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,275
Office Version
  1. 365
Platform
  1. Windows
It's still not clear to me what you are actually trying to do.

What's actually in the array, and what do you want returned in Range("C"& x)?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

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