Assigning Value of a formula to a cell

Question

I have a data chart with many products.

I want to filter each type of product, calculate the total quantity of that type as well as the number of product inside that type. And finally put the value of that function into a column in Sheet 2.

Here is the code. The quantity column is column U. It gets error 1004: Argument not optional, and it highlights the Set .... = FunctionR1C1 = .... part

Function T_Quantity()
    ActiveSheet.Range("U").Select
    Total = FunctionR1C1 = "=subtotal(9,C[0])"
End Function

Function T_Count(ref_column)
    ActiveSheet.Range("U").Select
    Total = FunctionR1C1 = "=subtotal(2,C[0])"
End Function

Sub Total_Count()
Dim my_array() As String
Dim iLoop As Integer
Dim iCount As Integer

iCount = 1
ReDim my_array(3)

my_array(0) = "=M1747B"
my_array(1) = "=M1747C"
my_array(2) = "=M1766B"

For iLoop = LBound(my_array) To UBound(my_array)

    ActiveSheet.Range("A:BB").Select

    Selection.AutoFilter Field:=15, Criteria1:=my_array
    Application.CutCopyMode = False

    'Calculate the quantity and no of lot, put in colum A,B in sheet 2'
    Set Worksheets("Sheet2").Cells(iCount, 1) = T_Quantity()
    Set Worksheets("Sheet2").Cells(iCount, 2) = T_Count()
    Application.CutCopyMode = False

    iCount = iCount + 1

Next iLoop

End Sub

Answer 1

Let's start with this and see if that gets you any closer to your desired results:

Sub Total_Count()
Dim my_array() As String
Dim iLoop As Integer
Dim iCount As Integer

iCount = 1

ReDim my_array(3)
my_array(0) = "=M1747B"
my_array(1) = "=M1747C"
my_array(2) = "=M1766B"

For iLoop = LBound(my_array) To UBound(my_array)
    ActiveSheet.Range("A:BB").Select
    Selection.AutoFilter Field:=15, Criteria1:=my_array
    Application.CutCopyMode = False

    'Calculate the quantity and no of lot, put in colum A,B in sheet 2'
    Worksheets("Sheet2").Cells(iCount, 1).FormulaR1C1 = "=subtotal(9,C[0])"
    Worksheets("Sheet2").Cells(iCount, 2).FormulaR1C1 = "=subtotal(2,C[0])"
    Application.CutCopyMode = False
    iCount = iCount + 1

Next iLoop

End Sub

What I changed:

• Eliminate the Set keyword when working with cell objects on the Worksheet. Set is used to assign object variables.
• Since the functions you call appear to be simply setting the cell's FormulaR1C1 property, I add the .FormulaR1C1 property to those lines, and then, instead of using a Function, I simply put the function's R1C1 notation directly in this subroutine.