Subtotal

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

treasuresflemar - 11 Dec 2007 22:14 GMT

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

Reply to this Message

Joel - 11 Dec 2007 22:33 GMT

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

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

> Why won't this or any other way I have tried to write this line work?
>
[quoted text clipped - 16 lines]
>
> End With

Reply to this Message

treasuresflemar - 11 Dec 2007 22:40 GMT

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

still returns Argument not optional error

> from
> rng = ("b" & rc & ":" & "l" & rc)
[quoted text clipped - 22 lines]
>>
>> End With

Reply to this Message

Jim Thomlinson - 11 Dec 2007 23:02 GMT

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???

Signature

HTH...

Jim Thomlinson

> Why won't this or any other way I have tried to write this line work?
>
[quoted text clipped - 16 lines]
>
> End With

Reply to this Message

treasuresflemar - 11 Dec 2007 23:09 GMT

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

> 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
[quoted text clipped - 21 lines]
>>
>> End With

Reply to this Message

Jim Thomlinson - 11 Dec 2007 23:50 GMT

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.

Signature

HTH...

Jim Thomlinson

> What I am totaling is currency.
> I have set x to currency, double, long and as a variant
[quoted text clipped - 28 lines]
> >>
> >> End With

Reply to this Message

Jim Thomlinson - 12 Dec 2007 00:03 GMT

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...

Signature

HTH...

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
[quoted text clipped - 33 lines]
> > >>
> > >> End With

Reply to this Message

treasuresflemar - 12 Dec 2007 21:32 GMT

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

Thanks for

> 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.
[quoted text clipped - 36 lines]
>> >>
>> >> End With

Reply to this Message