VBA Dynamic subtotal array

HI,

I need to subtotal only months column in a table (12 cols),  number of text column might vary all the time based on the select (max upto 8) but irrespective text column selection i need to subtotal months column

thanks,

Raghu

Top Contributor Asked on January 11, 2017 in VBA.
Add Comment
2 Answer(s)

HI,

Here is the solution for your query.

Sub Add_Month_Col_Details()
Dim MyHdr() As Variant
MyHdr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
'Find last row
lastRow = Sheet2.Cells.SpecialCells(xlLastCell).Row
lastCol = Sheet2.Cells.SpecialCells(xlLastCell).Column
For i = LBound(MyHdr) To UBound(MyHdr)
shdr = MyHdr(i)
For j = 1 To lastCol
If Sheet2.Cells(1, j) = shdr Then
Sheet2.Cells(lastRow + 1, j) = Application.WorksheetFunction.Sum(Sheet2.Range(Sheet2.Cells(1, j), Sheet2.Cells(lastRow, j)))
Exit For
End If
Next
Next
End Sub

Hope it clarifies your query.

Thanks!

Expert Answered on January 11, 2017.

Thanks a lot for the code, however I declared dimensions for –

Dim LastRow As Long
Dim lastCol As Long
Dim i As Long
Dim j As Long
Dim shdr() As Variant,

Am getting compile error for the below line

“If Sheet2.Cells(1, j) = shdr Then”, please advise & 

can we make this much more dynamic for array name “MyHdr = Array(“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”)” because 

headers depends on whether its actual or planned and names will be change accordingly like –

1st instance – “Actual -Jan”, “Plan -Feb“, “Plan -Mar”, “Plan -Apr”, “Plan -May”, “Plan -Jun”, “Plan -Jul”, “Plan -Aug”, “Plan -Sep”, “Plan -Oct”, “Plan -Nov”, “Plan -Dec”

2nd instance – “Actual -Jan”, “Actual -Feb“, “Plan -Mar”, “Plan -Apr”, “Plan -May”, “Plan -Jun”, “Plan -Jul”, “Plan -Aug”, “Plan -Sep”, “Plan -Oct”, “Plan -Nov”, “Plan -Dec”

please advise the solution, else even i can send the demo excel file for you, if you can send me the mail id

thanks,

raghu

on January 11, 2017.
Add Comment

 

Thanks a lot for the code, however I declared dimensions for –

Dim LastRow As Long
Dim lastCol As Long
Dim i As Long
Dim j As Long
Dim shdr as string

Am getting subtotal of entire column at the bottom, am not getting breakups subtotal, this code is giving Grand total only, please advise 

can we make this much more dynamic for array name “MyHdr = Array(“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”)” because – headers depends on whether its actual or planned and names will be change accordingly like –

1st instance – “Actual -Jan”, “Plan -Feb“, “Plan -Mar”, “Plan -Apr”, “Plan -May”, “Plan -Jun”, “Plan -Jul”, “Plan -Aug”, “Plan -Sep”, “Plan -Oct”, “Plan -Nov”, “Plan -Dec”

2nd instance – “Actual -Jan”, “Actual -Feb“, “Plan -Mar”, “Plan -Apr”, “Plan -May”, “Plan -Jun”, “Plan -Jul”, “Plan -Aug”, “Plan -Sep”, “Plan -Oct”, “Plan -Nov”, “Plan -Dec”

Hint is :The month column always starts from column 9 in sheet1, when the user select the required column in list box then the columns are printed in sheet2, Question – Can we give cell reference to the subtotal Array, so that it picks up the header dynamically????

please advise the solution, else even i can send the demo excel file for you, if you can send me the mail id

thanks,

raghu

Top Contributor Answered on January 12, 2017.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.