Forum Discussion

Bronze Contributor

Jul 06, 2018

Solved

replacing as variables in sort,subtotal macros

In the macro for Sorting (below), I wish to replace "C2:C" with a variable (mrng), I wonder what is the right DIM - as variant? string? also, In the macro for SubTotal (below), I wish to replace th...

excel

Macros and VBA

Wyn Hopkins
Jul 06, 2018
I see it's in the previous routine, you therefore need to pass it along with the other variables to the SortSubtotal routine

Lorenzo Kim

Bronze Contributor

Jul 06, 2018

Mr Hopkins

Thank you for your reply and advice.

the macros are already in place and working, I just wanted to trim down the codes to make it more modular.

I placed (SUB below) in a module, then I call it in another by:

SortSubtotal("client","C2:C",3) ;

there is compile error := expected and I think the error is in the 3,

could you help me straigthen this out.

many many thanks

Sub SortSubtotal(msheet As Variant, mrng As Range, mcol As Integer)

Sheets(msheet).Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets(msheet).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(msheet).Sort.SortFields.Add Key:=Range(mrng & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(msheet).Sort
.SetRange Range("A1:J" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1:J" & LastRow).Select
Selection.Subtotal GroupBy:=mcol, Function:=xlSum, TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

the mcol above may be the culprit..

Wyn Hopkins

MVP

Jul 06, 2018

I think both msheet and mrng should be String variables.

While your code is working now you should make the effort to make it as "bullet proof" as possible.

Just friendly advice as referencing Range("A1:J" & LastRow) can cause heartache further down the line.

Out of interest what is the significance of the prefix m you are using?

Can you post the message that appears when you run the Debugging Compile process

Also, stepping through your code using the F8 key will show you which step causes the issue

Lorenzo Kim
Bronze Contributor
Jul 06, 2018
Mr. Hopkins
the one I'm having problem with is almost the same as the following SUBS, but they work!
for your reference.. many thanks

Sub ClearSheets()
Dim LastRow As Long
ClearSheet ("client")
ClearSheet ("item")
ClearSheet ("class")
ClearSheet ("status")
ClearSheet ("resolve")
End Sub

Sub ClearSheet(msheet As Variant)
Sheets(msheet).Select
Cells.Select
Selection.RemoveSubtotal
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Range("A2:J" & LastRow).Select
Selection.ClearContents
End Sub
Lorenzo Kim
Bronze Contributor
Jul 06, 2018
Mr. Hopkins
Thank you for your prompt reply.
I'm attaching herewith the compile error image.
the prefix "m" for variable names I am using is a habit I acquired from FoxBase programming - long time ago...
To give you a better understanding of my workbook..
worksheet "RaD" is where the data is (a compilation of items returned/damaged from various clients) . It is then copied to 5 worksheets named "client", "item", "class", "status", "resolve" for analysis.
In worksheet "client" it is sorted on client then subtotaled at field "QTY", and in "item" - it is sorted on item then subtotaled at field "QTY" .. and so on... quite a repetitive task. Hope you now get a clearer picture. I wouldn't ask for a "true" codes but rather a short fix for the running macros I created by "macro recording". I think the culprit is in mcol.
many many thanks