User defining a range before he executes macro

[jase]

I don’t know VBA, I only know how to use the Excel macro recorder and then go and edit / play around with the code.

That said, I have some code that sorts in a descending order according to a set range. Here is the part of the code in question:

Range("D8:J305").Select

Now what I’d like to do is have the user determine the range that is to be sorted. I’d like to be able to put in a value in cell A1 that reads “D8:J100” or perhaps “D8:J58”, etc. You get the idea. I want to be able to manually set my range before executing my macro.

Can anyone tell me how I could modify my code so that it would actually look at the user defined range in cell A1 and then execute it’s sorting steps based upon that unique range? I tried the following but it did not work:

Range(A1).Select

I also tried:

Range(“A1”).Select

Neither worked.

How can I modify the visual basic code so that it is getting it’s range data from cell A1?

Thanks for any help.
jase.

PS,FYI, the entire code is thus:

Sub CurrentlySortAscending()
'' CurrentlySortAscending Macro
' Macro recorded 11/5/2002 by J. Sibley
'
Range("D8:J305").Select
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("H9"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D4:J4").Select
End Sub

 

[DonkeyOte]

On 2002-11-05 07:45, jase wrote:
I don’t know VBA, I only know how to use the Excel macro recorder and then go and edit / play around with the code.

That said, I have some code that sorts in a descending order according to a set range. Here is the part of the code in question:

Range("D8:J305").Select

Now what I’d like to do is have the user determine the range that is to be sorted. I’d like to be able to put in a value in cell A1 that reads “D8:J100” or perhaps “D8:J58”, etc. You get the idea. I want to be able to manually set my range before executing my macro.

Can anyone tell me how I could modify my code so that it would actually look at the user defined range in cell A1 and then execute it’s sorting steps based upon that unique range? I tried the following but it did not work:

Range(A1).Select

I also tried:

Range(“A1”).Select

Neither worked.

How can I modify the visual basic code so that it is getting it’s range data from cell A1?

Thanks for any help.
jase.

PS,FYI, the entire code is thus:

Sub CurrentlySortAscending()
'' CurrentlySortAscending Macro
' Macro recorded 11/5/2002 by J. Sibley
'
Range("D8:J305").Select
ActiveWindow.ScrollRow = 1
Selection.Sort Key1:=Range("H9"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D4:J4").Select
End Sub

Jase....would attack this way...

STARTCELL = InputBox("Please Enter The Cell Reference for the Start of the Copy Range - eg A1","START CELL")

ENDCELL = InputBox("Please Enter The Cell Reference for the End of the Copy Range - eg D10","END CELL")

Range(STARTCELL & ":" & ENDCELL).Select

Looking at your macro you may have want to install a check on the ranges to ensure they cover the necessary cells (e.g. your example uses H9 in the sort, this will fail if H9 is not selected etc...)

 

[gnaga]

Try to store the content of A1 in a variable and then use like following

MyRange = Range("A1).Value
Range(MyRange).Select
Then proceed with your code

GNaga

 

[jase]

GNaga

I tried using the MyRange idea, just as you typed it with but one exception – I’m assuming you meant to put closing quotes around the A1.

Anyway, when I tried it, I get the following VB Error message:

Run-time Error ‘1004’
Method ‘Range’ of object ‘_global’ failed

Any thoughts?

 

[jase]

LASW10,

I like the code; I learned something new

The only problem is that I really want the Range to reference a cell that has something like “D5:R58” in it. I tried CNaga's method but I couldn’t get it to work. I really wish I could get that method to work. Any ideas?

And thanks for the code. I don’t want to use input boxes in this example but I have saved your suggestion and will certainly use it later. Thanks.

jase.

 

[DonkeyOte]

Not sure what's happening here but I typed in A1 - "A2:D50"

then in VB

RG = Sheets("sheet1").Range("a1")

Range(RG).Select

and it worked - it selected the range ok.

 

[jase]

LASW10,

I like the code; I learned something new

The only problem is that I really want the Range to reference a cell that has something like “D5:R58” in it. I tried CNaga's method but I couldn’t get it to work. I really wish I could get that method to work. Any ideas?

And thanks for the code. I don’t want to use input boxes in this example but I have saved your suggestion and will certainly use it later. Thanks.

jase.