Can anybody help to fix this please ?

M

michael.beckinsale

Hi All,

The following code is supposed to build an array from 8 to 105
(columns i want to subtotal) and then use that array in the subtotal
function.

I think the problem lies with the data type in that the array is built
as a string.

Can anybody help to fix this code please ?


Sub SP_SubTotals()

Dim myCols As Variant
Application.ScreenUpdating = False
Sheets("Staff Planner").Activate
myCols = 8
For i = 9 To LC
myCols = myCols & "," & i
Next i
MsgBox (myCols)
If IsArray(myCols) Then
MsgBox ("OK")
Range(Cells(3, 1), Cells(LR, LC)).Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=myCols,
_
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End If

End Sub

TIA

Regards

Michael Beckinsale
 
P

Peter T

Hi Michael,

With the info you've provided not sure how to reconstruct your code. I did
it one way which worked giving outlines & subtotals, subject to some labels
and data in the cells.

As for your array, where is it, myCols is a string.

ReDim myCols(9 To LC)
For i = lBound(myCols) To LC
myCols(i) = i
Next i

Regards,
Peter T
 
J

Joel

I haven't use subtotals before. It has too methods a worksheet method and a
range method. It looks like you where trying to mix some of each. You also
weren't using arrays properly and it wasn't needed. the real problem was LR,
and LC were not defined. This will get you started. there is a warning
message about not having header columns and row that may appear, you can just
continue to get results.

Sub SP_SubTotals()

Dim myCols As Variant
Application.ScreenUpdating = False
Sheets("Staff Planner").Activate
myCols = 8
LC = 20
LR = 10

Range(Cells(3, 1), Cells(LR, LC)).Select
Selection.Subtotal GroupBy:=4, Function:=xlSum, _
TotalList:=Array(2, 3), _
Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True

End Sub
 
M

michael.beckinsale

Hi Peter & Joel,

Peter - Thanks very much, it was the building of the array that caused
the problem (l obviously need to 'bone up' on arrays) and as you
correctly state l had built a string which l suspected was causing the
problem, l was trying to convert it to an array without success!

My code now does as required.

Joel - See above re array plus LC & LR are functions within the
project that can be used whenever necessary to return the LastRow &
LastColumn. I do need the array as the 'target' columns are dynamic
and will always be about 120 in number, hence doing so using VBA but
thank you for your input.

I always appreciate a response and usually end up learning something.

Once again many thanks to you both

Regards

Michael beckinsale
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top