Subtotal

[treasuresflemar]

Why won't this or any other way I have tried to write this line work?

Set x = rng.Subtotal(groupby:=1, Function:=xlSum)

Argument not optional error

Dim x as long

With Range(rn) 'tells range to use

rc = .Find(DateValue(dt), LookIn:=xlValues,
_lookat:=xlWhole).Rows.Row
'get row number of match

rng = ("b" & rc & ":" & "l" & rc)
'sets range to subtotal

Set x = rng.Subtotal(groupby:=1, Function:=xlSum)


End With

 

[Joel]

from
rng = ("b" & rc & ":" & "l" & rc)

to
set rng = Range("b" & rc & ":" & "l" & rc)

 

[treasuresflemar]

x = rng.Subtotal(groupby:=1, Function:=xlSum)
and
set x = rng.Subtotal(groupby:=1, Function:=xlSum)

still returns Argument not optional error

 

[Jim Thomlinson]

What are you trying to do??? You have declared X as long. Then you try to
assign it to the Subtotal Method of the range. What number were you thinking
would end up in X since subtotal adds subtotals to a range of values???

 

[treasuresflemar]

What I am totaling is currency.
I have set x to currency, double, long and as a variant

Has no bearing on setting x
all return same error
compile error: Argument not optional with subtotal highlighted

 

[Jim Thomlinson]

Subtotal is a method of a range. It inserts rows into the range and adds
subtotal lines. It does not have a return value. It transforms the range. So
it does not matter how you declare x since subtotal does not return anything
to put in x. So what are you trying to do.

 

[Jim Thomlinson]

Specific to your Argument not optional question. Subtotal has 3 required
arguments. GroupBy, Function and TotalList. You have only listed 2... Group
by and Function. What is the list of columns that you want totaled??? Even if
you supply that list it still will not return a value or object to x...

 

[treasuresflemar]

I was trying to use subtotal like sum in the workbooks. Brain Fart.
I got it working with Application......Sum()

Thanks for