Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anybody help to fix this please ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anybody help to fix this please ?
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 "michael.beckinsale" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anybody help to fix this please ?
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anybody help to fix this please ?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|