Can anybody help to fix this please ?

[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

 

[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

 

[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

 

[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