Combining two subroutines

[25INF]

My goal is to use the range objects from subroutine A toexecute subroutine B. To do this, I’d like to combine subroutine B intosubroutine A.


I was going to attach the spreadsheet to show you what I mean, but I don't have permissions to do that. Basically, if you could help me on my syntax (I underlined what I mean), I'm sure this will work.

I also posted both subroutines below as a quick overview, with my notes throughout. While I think I understand what needs to happen, the syntax is killing me . The code is as follows (Any help would be appreciated):

<strike></strike>

Option Explicit
Sub A_Subtotal()
 
[/COLOR][COLOR=#ff0000]'Step1: Select "A14" for the first inputbox(EXAMPLE ONLY)[/COLOR]
[COLOR=#ff0000][/COLOR]
[COLOR=#ff0000]'Step2: Select "A15:A22" for the second inputbox(EXAMPLE ONLY)[/COLOR]
[COLOR=#000000] 
On Error Resume Next
Dim X, Y, Z As Object
Dim I As Long
    Set X =Application.InputBox(Prompt:="Select a cell from the desired break.",Title:="X-Axis.", Type:=8)
    Set Y =Application.InputBox(Prompt:="Select y-axis range. Avoid mergedcells.", Title:="Y-Axis.", Type:=8)
If X Is Nothing Then Exit Sub
    For I = 13 To 33
           Cells(X.Row, I).Value = WorksheetFunction.Sum(Y.Offset(, I + (1 -Y.Column) - 1))
    Next I
On Error GoTo 0
End Sub
 
Sub B_Sort()
 
[/COLOR][COLOR=#ff0000]'Step1: Select "A15:AG22" (EXAMPLE ONLY)[/COLOR]
[COLOR=#ff0000][/COLOR]
[COLOR=#ff0000]'PROBLEM: Use the range objects from subroutine A to executeSubroutine B.[/COLOR]
[COLOR=#ff0000][/COLOR]
[COLOR=#ff0000]'NOTE: I must ensure the user can define the range objects via theinput boxes since the row numbers are always dynamic.[/COLOR]
[COLOR=#000000] 
Dim Z As Object
Set Z = Application.InputBox(Prompt:="Select a cellfrom the desired break.", Title:="X-Axis.", Type:=8)
 
[/COLOR][U][B][COLOR=#ff0000]' The range to sort will always be Range("A" &X.Row & ":AG" & Y.Row) with x.row offset down 1 (I can't seemto get my syntax right when writing this).[/COLOR][/B][/U]
[U][B][COLOR=#ff0000][/COLOR][/B][/U]
[U][B][COLOR=#ff0000]' The key range will always be M Column with X.Row offsetdown 1 to Y.Row. The same thing will then be used for each column with AGcolumn being the last one.[/COLOR][/B][/U]
[COLOR=#000000] 
    Z.SortKey1:=Range("M15:M22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("N15:N22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("O15:O22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("P15:P22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("Q15:Q22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("R15:R22"), Order1:=xlAscending, dataoption1:=xlSortNormal
    Z.SortKey1:=Range("S15:S22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("T15:T22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("U15:U22"), Order1:=xlAscending, dataoption1:=xlSortNormal
    Z.SortKey1:=Range("V15:V22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("W15:W22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("X15:X22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.Sort Key1:=Range("Y15:Y22"),Order1:=xlAscending, dataoption1:=xlSortNormal
    Z.SortKey1:=Range("Z15:Z22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("AA15:AA22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("AB15:AB22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("AC15:AC22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("AD15:AD22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.Sort Key1:=Range("AE15:AE22"),Order1:=xlAscending, dataoption1:=xlSortNormal
    Z.SortKey1:=Range("AF15:AF22"), Order1:=xlAscending,dataoption1:=xlSortNormal
    Z.SortKey1:=Range("AG15:AG22"), Order1:=xlAscending,dataoption1:=xlSortNormal
 
End Sub

 

[25INF]

I marked the cross-post on EXCELFORUM as solved since it had received no attention anyways. I only canceled the post on EXCELFORUM instead of MREXCEL because Shg had taken the time to answer this post on MREXCEL. My response to his post is my own work (not from the excelforum), and I'm still looking for help. Thank you.

 

[25INF]

Please note that I'm guilty of cross-posting on excelforum BUT, I canceled that forum thread by marking it as solved even though it received no activity. However, if you'd like to see the attachment I'm talking about please visit excelforum to see a prototype of what I'm talking about. ​

https://www.excelforum.com/excel-pro...broutines.html

Otherwise, I will do my best to explain myself in words. To start, I have a spreadsheet where I subtotal various subsystems of a system (I'm an estimator by trade). Since the number of subsystems for any given system can be different, I created a code that asks what row to post the subtotals. This is the range object which you defined as "rX" in your revised code. The second input box asks for the number of rows (this is where it totals up the number of subsystems as determined by the user) by allowing the user to drag and select the number of rows to include in the subtotal. This is the range object you defined as "rY". To further add to matters, each row that contains a subsystem always has a specific number of "points" by subsystem. These points are categorized in Columns "M thru AG".

The loop where you see I SUM the totals of each column using rX and rY works like this:

(1) rx is the row where the user posts the subtotals
(2) ry is the number of rows that contain a subsystem. each subsystem has a specific number of points categorized by columns "M" thru "AG". the loop that you see below

totals the number of points by subsystem for each column beginning at "M" and ending at "33" (range rY) and places them along the rx row. It works as follows:​

[FONT=Arial][SIZE=2][COLOR=#000000]For i = 13 To 33
[/COLOR][/SIZE][/FONT][SIZE=2][LEFT][COLOR=#000000][FONT=Arial][FONT=Arial][COLOR=#000000]     Cells(rX.Row,i).Value = WorksheetFunction.Sum(rY.Offset(, i + (1 - rY.Column) - 1)) 
 [/COLOR][/FONT][/FONT][/COLOR][/LEFT][/SIZE][SIZE=2][LEFT][COLOR=#000000][FONT=Arial][FONT=Arial][COLOR=#000000]Next I[/COLOR][/FONT][/FONT][/COLOR][/LEFT][/SIZE]
[LEFT][COLOR=#222222][FONT=Verdana]

[/FONT][/COLOR][/LEFT]

What I had done previously is I made a second subroutine where the user selects the range he/she would like to sort. But here's the thing, this range is always the same as what the user subtotals...kind of. So, I thought, why not combine the sort subroutine (B) into the sum subroutine (A) and use the same range objects as subroutine (A). That way the sort subroutine uses the same range object as defined by ry. For ry to work as a range object to sort, I would have to take the number of rows defined by the ry range object and expand it to select columns A thru AG then I think this would work (atleat that's what I try and explain in one of my former posts above).