Subtotal problem/conflict

[Austin]

I am having some issue with this but not sure what it is. I am guessing the
Columns2 variant is not being created for one reason or another but have not
been able to figure out why. Thanks for the help:


ColStart = 2
ColEnd = lvlColumn + 2

ReDim Columns(0 To (ColEnd - ColStart))
For i = ColStart To ColEnd
Columns(i - ColStart) = i
Next i

ColSt = 3
ColEn = lvlColumn + 2

ReDim Columns2(0 To (ColEn - ColSt))
For i = (ColSt + 1) To ColEn
Columns(i - ColSt) = i
Next i

With xlSheet.Range("A:ZZ")
.Cells.Copy
.Cells.PasteSpecial (xlPasteValues)
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Columns, _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
.Replace What:="0", Replacement:="", lookAt:=xlWhole
.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Columns2, _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
End With

 

[GB]

In your second For loop, you have the variable columns instead of columns2.

Also, it would appear that since you have colst = 3, within your second for
loop you start your data at columns2(1) instead of columns2(0). Another
thing, if your lvlColumn is = 0, then your redim of columns2 will result in a
negative value [(0+2) - 3] and if your value of lvlColumn = 1, then you will
have just one value to assign columns2(0), then when you go to loop on the
second loop you will have for i = 4 to 3 which will bypass the loop. Next,
if lvlcolumn = 2 then columns2 will be from 0 to 1, loop will be from 4 to 4,
columns2(4-3) [columns2(1)] = 4 and no value will be assigned to columns2(0).

I am not familiar with all of the things in the xlSheet range, but seeing
that it seems to be excel related, I would recommend going to the Excel VBA
programming area, instead of this area which is Access related. (Even though
you may be trying to use access to perform those functions, it is more excel
related) As for the top part of the code, really doesn't matter which
program it is in, you run the chance of the myriad of problems that are
described above.