johnb25
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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]".