Subtotal
 

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

Subtotal
 

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

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


"treasuresflemar" wrote:

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

Subtotal
 

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

still returns Argument not optional error


"Joel" wrote in message
...
from
rng = ("b" & rc & ":" & "l" & rc)

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


"treasuresflemar" wrote:

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

Subtotal
 

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

Jim Thomlinson


"treasuresflemar" wrote:

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

Subtotal
 

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" wrote in message
...
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???
--
HTH...

Jim Thomlinson


"treasuresflemar" wrote:

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

Subtotal
 

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

Jim Thomlinson


"treasuresflemar" wrote:

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" wrote in message
...
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???
--
HTH...

Jim Thomlinson


"treasuresflemar" wrote:

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

Subtotal
 

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

Jim Thomlinson


"Jim Thomlinson" wrote:

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

Jim Thomlinson


"treasuresflemar" wrote:

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" wrote in message
...
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???
--
HTH...

Jim Thomlinson


"treasuresflemar" wrote:

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