User defining a range before he executes macro

jase

Board Regular
Joined
Nov 4, 2002
Messages
62
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

Board Regular
Joined
Nov 4, 2002
Messages
62
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

Board Regular
Joined
Nov 4, 2002
Messages
62

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Board Regular
Joined
Nov 4, 2002
Messages
62
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.
 

Forum statistics

Threads
1,144,741
Messages
5,726,014
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top