ExcelBanter - problem passing an argument to a sub

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - problem passing an argument to a sub (https://www.excelbanter.com/excel-programming/331941-problem-passing-argument-sub.html)

natanz

problem passing an argument to a sub

I have the following code that I use to sort a range of data.

Public Sub sorter()
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I am trying to rewrite it, so that I can pass two arguments to it, the
first argument would be in place of the "b3" in the second line, and
the other argument would replace "S3" in the fifth line of the code
above.

now my code looks like this:

Public Sub sorter(RangeStart, SortKey)
Range(RangeStart, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

and i was using a call to this procedure that looks like this:
sorter (b3, s3)

but that doesn't work. Can someone tell me what i am doing wrong? I
am new at this, and learning on my own so any help is appreciated. Can
I pass a literal as an argument to a procedure? Thanks in advance.

Nigel

problem passing an argument to a sub

Hi,

Make the following change to the sorter procedure by delcaring the
parameters as strings......

Public Sub sorter(RangeStart As String, SortKey As String)

Then call the procedure using......

Call sorter("B3", "S3")

--
Cheers
Nigel

"natanz" wrote in message
...
I have the following code that I use to sort a range of data.

Public Sub sorter()
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I am trying to rewrite it, so that I can pass two arguments to it, the
first argument would be in place of the "b3" in the second line, and
the other argument would replace "S3" in the fifth line of the code
above.

now my code looks like this:

Public Sub sorter(RangeStart, SortKey)
Range(RangeStart, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

and i was using a call to this procedure that looks like this:
sorter (b3, s3)

but that doesn't work. Can someone tell me what i am doing wrong? I
am new at this, and learning on my own so any help is appreciated. Can
I pass a literal as an argument to a procedure? Thanks in advance.

Toppers

problem passing an argument to a sub

Hi,
Use Call Sorter("B3","S3")

HTH

"natanz" wrote:

I have the following code that I use to sort a range of data.

Public Sub sorter()
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I am trying to rewrite it, so that I can pass two arguments to it, the
first argument would be in place of the "b3" in the second line, and
the other argument would replace "S3" in the fifth line of the code
above.

now my code looks like this:

Public Sub sorter(RangeStart, SortKey)
Range(RangeStart, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

and i was using a call to this procedure that looks like this:
sorter (b3, s3)

but that doesn't work. Can someone tell me what i am doing wrong? I
am new at this, and learning on my own so any help is appreciated. Can
I pass a literal as an argument to a procedure? Thanks in advance.

Toppers

problem passing an argument to a sub

Hi,
Call Sorter("B3","S3")

HTH

"natanz" wrote:

I have the following code that I use to sort a range of data.

Public Sub sorter()
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I am trying to rewrite it, so that I can pass two arguments to it, the
first argument would be in place of the "b3" in the second line, and
the other argument would replace "S3" in the fifth line of the code
above.

now my code looks like this:

Public Sub sorter(RangeStart, SortKey)
Range(RangeStart, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

and i was using a call to this procedure that looks like this:
sorter (b3, s3)

but that doesn't work. Can someone tell me what i am doing wrong? I
am new at this, and learning on my own so any help is appreciated. Can
I pass a literal as an argument to a procedure? Thanks in advance.

Stefi

problem passing an argument to a sub

Call sorter("B3", "S3")
Stefi

€žnatanz€ ezt Ã*rta:

I have the following code that I use to sort a range of data.

Public Sub sorter()
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I am trying to rewrite it, so that I can pass two arguments to it, the
first argument would be in place of the "b3" in the second line, and
the other argument would replace "S3" in the fifth line of the code
above.

now my code looks like this:

Public Sub sorter(RangeStart, SortKey)
Range(RangeStart, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

and i was using a call to this procedure that looks like this:
sorter (b3, s3)

but that doesn't work. Can someone tell me what i am doing wrong? I
am new at this, and learning on my own so any help is appreciated. Can
I pass a literal as an argument to a procedure? Thanks in advance.

Stefi

problem passing an argument to a sub

Call sorter("B3","S3")
Regards, Stefi

€žnatanz€ ezt Ã*rta:

I have the following code that I use to sort a range of data.

Public Sub sorter()
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I am trying to rewrite it, so that I can pass two arguments to it, the
first argument would be in place of the "b3" in the second line, and
the other argument would replace "S3" in the fifth line of the code
above.

now my code looks like this:

Public Sub sorter(RangeStart, SortKey)
Range(RangeStart, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

and i was using a call to this procedure that looks like this:
sorter (b3, s3)

but that doesn't work. Can someone tell me what i am doing wrong? I
am new at this, and learning on my own so any help is appreciated. Can
I pass a literal as an argument to a procedure? Thanks in advance.

K Dales[_2_]

problem passing an argument to a sub

You need to supply the cell addresses as strings, the way you wrote it, so
call it as:
Sorter("b3","s3")

"natanz" wrote:

I have the following code that I use to sort a range of data.

Public Sub sorter()
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I am trying to rewrite it, so that I can pass two arguments to it, the
first argument would be in place of the "b3" in the second line, and
the other argument would replace "S3" in the fifth line of the code
above.

now my code looks like this:

Public Sub sorter(RangeStart, SortKey)
Range(RangeStart, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

and i was using a call to this procedure that looks like this:
sorter (b3, s3)

but that doesn't work. Can someone tell me what i am doing wrong? I
am new at this, and learning on my own so any help is appreciated. Can
I pass a literal as an argument to a procedure? Thanks in advance.

bac

problem passing an argument to a sub

Probably because the call is trying to pass variables named b3 and s3 that
don't exist?

in the Calling routine

Dim RangeStart, SortKey as Range

Then set them to the desired values:

RangeStart = "A3"
SortKey = "S3"

then call the sub:

sorter RangeStart, SortKey (No ())

BAC

"natanz" wrote:

I have the following code that I use to sort a range of data.

Public Sub sorter()
Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

I am trying to rewrite it, so that I can pass two arguments to it, the
first argument would be in place of the "b3" in the second line, and
the other argument would replace "S3" in the fifth line of the code
above.

now my code looks like this:

Public Sub sorter(RangeStart, SortKey)
Range(RangeStart, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range(SortKey), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False,Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

and i was using a call to this procedure that looks like this:
sorter (b3, s3)

but that doesn't work. Can someone tell me what i am doing wrong? I
am new at this, and learning on my own so any help is appreciated. Can
I pass a literal as an argument to a procedure? Thanks in advance.

All times are GMT +1. The time now is 03:02 PM.