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

 

[shg]

Welcome to the board.

Perhaps ...

Option Explicit

Sub Subtotal()
  Dim rX            As Range
  Dim rY            As Range
  Dim rZ            As Range
  Dim i             As Long
  Dim cell          As Range

  On Error Resume Next

  Set rX = Application.InputBox(Prompt:="Select a cell from the desired break.", Title:="X-Axis.", Type:=8)
  If rX Is Nothing Then Exit Sub

  Set rY = Application.InputBox(Prompt:="Select y-axis range. Avoid mergedcells.", Title:="Y-Axis.", Type:=8)
  If rY Is Nothing Then Exit Sub

  For i = 13 To 33
    Cells(rX.Row, i).Value = WorksheetFunction.Sum(rY.Offset(, i + (1 - rY.Column) - 1))
  Next i

  Set rZ = Application.InputBox(Prompt:="Select a cellfrom the desired break.", Title:="X-Axis.", Type:=8)
  If rZ Is Nothing Then Exit Sub

  On Error GoTo 0

  For Each cell In Range("M15:AA15").Cells
    rZ.Sort Key1:=cell
  Next cell
End Sub

 

[jeffreybrown]

X-Post

 

[25INF]

Thanks Shg for the response. You definitely cleaned up my code quite a bit. But I was trying to get rid of the third inputbox all together and just use input boxes rX and rY to both sum and sort. That way I could get rid of the rZ inputbox all together.

 

[25INF]

Shg,

now that I think of it, I could sort off of just the rY box you created in your code. rX is irrelevant (although I'd still need that for the sum function). The reason why is because we know that columns A thru AG will always be fixed. The only thing that is variable (which is defined by the rY box) is the number of rows. For example, say M15 thru M22 is selected by the rY input box. To sort, I just need to be able to turn M15 thru M22 into A15:AG22 for the sort range. For another example, say the rY input box selects V24:V32. To sort, I just need to be able to turn V24 thru V32 into A24:AG32...To quickly illustrate:

EXAMPLE 1
If M15:M22 is selected by the ry input box then
M15:M22 = A15:AG22.sort
End If

EXAMPLE 2
If V24:V32 is selected by the ry input box then
V24:V32 = A24:AG32.sort

If I could figure out how to write the above concept, I could eliminate the rZ inputbox altogether. Of course, the rY inputbox must remain dynamic, but based off the rows selection of the rY inputbox I could always be able to select the range.sort that I need... I think it would look something like this

[FONT=Arial][SIZE=2][FONT=Arial][SIZE=2][COLOR=#000000]Sub Subtotal()

[/COLOR][/SIZE][/FONT][SIZE=2][FONT=Arial][COLOR=#000000]Dim rX As Range
[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Arial][COLOR=#000000]Dim rY As Range
[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Arial][COLOR=#000000]Dim i As Long
[/COLOR][/FONT][/SIZE][SIZE=2][FONT=Arial][COLOR=#000000]Dim cell As Range[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Arial][COLOR=#000000]On Error Resume Next[/COLOR][/FONT][/SIZE]
[U][COLOR=#000000][FONT=Arial][SIZE=2]‘Sum subtotals using rX and rY[/SIZE][/FONT][/COLOR][/U]
[/SIZE][/FONT][FONT=Arial][SIZE=2][/SIZE][/FONT][FONT=Arial][SIZE=2][COLOR=#000000][SIZE=2][FONT=Arial][COLOR=#000000]Set rX =Application.InputBox(Prompt:="Select a cell from the desired break.",Title:="X-Axis.", Type:=8)
     [/COLOR][/FONT][/SIZE][/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=2][COLOR=#000000][SIZE=2][FONT=Arial][COLOR=#000000]If rX Is NothingThen Exit Sub[/COLOR][/FONT][/SIZE][/COLOR][/SIZE][/FONT]
[FONT=Arial][SIZE=2][COLOR=#000000][SIZE=2][FONT=Arial][COLOR=#000000]Set rY =Application.InputBox(Prompt:="Select y-axis range. Avoidmergedcells.", Title:="Y-Axis.", Type:=8)
     [/COLOR][/FONT][/SIZE][SIZE=2][FONT=Arial][COLOR=#000000]If rY Is NothingThen Exit Sub[/COLOR][/FONT][/SIZE]
[SIZE=2][FONT=Arial][COLOR=#000000]For i = 13 To 33
     [/COLOR][/FONT][/SIZE][SIZE=2][FONT=Arial][COLOR=#000000]Cells(rX.Row,i).Value = WorksheetFunction.Sum(rY.Offset(, i + (1 - rY.Column) - 1))
     [/COLOR][/FONT][/SIZE][SIZE=2][FONT=Arial][COLOR=#000000]Next I

[/COLOR][/FONT][/SIZE][FONT=Arial][SIZE=2][COLOR=#000000][U]‘Sort using rY[/U]

[/COLOR][/SIZE][/FONT][COLOR=#ff0000][FONT=Arial][SIZE=2]     [/SIZE][/FONT][FONT=Arial][SIZE=2]rY = (“A” & rY.Row & “:AG” & rY.Row)[/SIZE][/FONT][/COLOR]
[COLOR=#ff0000][SIZE=2][FONT=Arial]For Each cell in Range(“M15:AA15”).Cells           
     [/FONT][/SIZE][SIZE=2][FONT=Arial]rY.SortKey1:=cell
[/FONT][/SIZE][FONT=Arial][SIZE=2]Next cell[/SIZE][/FONT][/COLOR]
[/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=2][COLOR=#000000][FONT=Arial][SIZE=2][COLOR=#000000]On Error GoTo 0
[/COLOR][/SIZE][/FONT][FONT=Arial][SIZE=2][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT]

 

[25INF]

of course I just tried the above code that I re-worked, my syntax doesn't work...

 

[Fluff]

Cross posted at https://www.excelforum.com/excel-programming-vba-macros/1215653-combining-two-subroutines.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[25INF]

Cross posted at https://www.excelforum.com/excel-programming-vba-macros/1215653-combining-two-subroutines.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

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

I cross posted. Please see the attached link for the other forum posting (which has received no activity as of yet). Please understand that I only cross posted because I was unable to post the attachment on this site. Since this was after the fact and I had no way of deleting the thread, I posted a second time on excelforum where I was able to provide the attachment. Consequently, I've been stuck fielding questions about a cross-post which I can't delete, even though I want to given that I can't post the attachment on this site.

 

[Fluff]

All we ask is that you let us know that you have cross-posted. That has been done & therefore all is ok.

if by this

Consequently, I've been stuck fielding questions about a cross-post which I can't delete, even though I want to given that I can't post the attachment on this site.

you mean that you want this thread closed, so that you can continue on the other board (where you have no response), then I can easily do that.
If you would prefer to keep this open, then that's fine.

 

[shg]

In the interest of me not needing to reverse-engineer your code, could you explain, in words and sentences, what you're trying to do?