Link to home
Start Free TrialLog in
Avatar of johnb25
johnb25Flag for Ireland

asked on

VBA WorksheetFunction- Set cell value to the the result of a formula

Hi,

I have a large table in Excel 2013.
I need to filter columns 58-81, one at a time, for the value FALSE.
I then need to store the number of records found each time.
My table starts on Row 2, and I am putting the number of records found in row 1 of the column being filtered.
Table name is FSD.

I created this VBA to try to do this, but keep getting this error message:
Run-time error '424': Object Required.
I have not tried to add the loops yet, until I can get one pass to work.

Sub CountFiltered()

Dim TabFieldName As Variant
    
    Range("BF1").Select
    ActiveSheet.ListObjects("FSD").Range.AutoFilter Field:=58, Criteria1:="FALSE"
    TabFieldName = "FSD[" & Cells(ActiveCell.Row + 1, ActiveCell.Column).Value & "]"
    
    ActiveCell.Value = Application.WorksheetFunction.Subtotal(3, TabFieldName)
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Rgonzo1971...but if he redefines TabFieldName as a String variable, there will be a compile error at runtime on line 9 (type mismatch).  I tried that, too!

I was mistaken about replacing the header row with numbers, but I forgot to mention that line 7 offsets down one row to try and determine the TabFieldName.  That won't work because the name would end up being either "FSD[TRUE]" or "FSD[FALSE]".
Avatar of johnb25

ASKER

Thanks both for your help...working code added below

Using TabFieldName without quotes works as a Range.

@Glenn_Ray Line 7 was offsetting from Row 1 (BF1), so the TabFieldName was correct.
Also, I used the subtotal because I am filtering for FALSE on each column in turn, and only reporting the number of records found.
Thanks for adding the Loop.

Sub CountMdrFiltered()

Dim TabFieldName As String
Dim intCol As Integer

For intCol = 58 To 81
    ActiveSheet.ListObjects("FSD").Range.AutoFilter Field:=intCol, Criteria1:="FALSE"
    TabFieldName = "FSD[" & Cells(2, intCol).Value & "]"
    Cells(1, intCol).Value = Application.WorksheetFunction.Subtotal(3, Range(TabFieldName))
Next intCol

End Sub

Open in new window