Ufdjrw

How much RAM could one put in a typical 80386 setup?

Theorems that impeded progress

Meaning of に in 本当に

How to format long polynomial?

Was any UN Security Council vote triple-vetoed?

RSA: Danger of using p to create q

Watching something be written to a file live with tail

When a company launches a new product do they "come out" with a new product or do they "come up" with a new product?

"You are your self first supporter", a more proper way to say it

Paid for article while in US on F-1 visa?

Why can't I see bouncing of a switch on an oscilloscope?

Today is the Center

Convert two switches to a dual stack, and add outlet - possible here?

Definite integral giving negative value as a result?

Why does Kotter return in Welcome Back Kotter?

Do infinite dimensional systems make sense?

Why "Having chlorophyll without photosynthesis is actually very dangerous" and "like living with a bomb"?

Approximately how much travel time was saved by the opening of the Suez Canal in 1869?

What's that red-plus icon near a text?

Fully-Firstable Anagram Sets

Why doesn't Newton's third law mean a person bounces back to where they started when they hit the ground?

Why is consensus so controversial in Britain?

NMaximize is not converging to a solution

Why is 150k or 200k jobs considered good when there's 300k+ births a month?

How to insert data in ascending order into an array in vba excel?

How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How to convert a column number (eg. 127) into an excel column (eg. AA)CopyOrigin on Insert in Excel VBADifference between Visual Basic 6.0 and VBAIs there a way to crack the password on an Excel VBA Project?Excel to CSV with UTF8 encodingHow to avoid using Select in Excel VBAVBA Code to sort an Excel Column in Ascending Order and Expand Selection?Merge two lists in Excel in ascending or descending ordervba code to insert row and data in ascending order

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

-1

In excel vba, I have a range of random numbers between 1 and 10 and they are not in order. What is the shortest way to insert these random number in an ascending order into an array? Also names in order from A to Z?

excel vba

share|improve this question

edited Mar 8 at 8:14

Pᴇʜ

25.1k63052

asked Mar 8 at 2:17

SasSas

1

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  What have you tried so far? Please share any code you have.
  
  – BigBen
  Mar 8 at 2:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Wouldn't simply using the "Sort & Filter" button in xcel do the trick. Seems vba is over-kill here..
  
  – alowflyingpig
  Mar 8 at 2:30
  

  
  
  
  

add a comment | 

-1

In excel vba, I have a range of random numbers between 1 and 10 and they are not in order. What is the shortest way to insert these random number in an ascending order into an array? Also names in order from A to Z?

excel vba

share|improve this question

edited Mar 8 at 8:14

Pᴇʜ

25.1k63052

asked Mar 8 at 2:17

SasSas

1

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  What have you tried so far? Please share any code you have.
  
  – BigBen
  Mar 8 at 2:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Wouldn't simply using the "Sort & Filter" button in xcel do the trick. Seems vba is over-kill here..
  
  – alowflyingpig
  Mar 8 at 2:30
  

  
  
  
  

add a comment | 

In excel vba, I have a range of random numbers between 1 and 10 and they are not in order. What is the shortest way to insert these random number in an ascending order into an array? Also names in order from A to Z?

excel vba

share|improve this question

edited Mar 8 at 8:14

Pᴇʜ

25.1k63052

asked Mar 8 at 2:17

SasSas

1

share|improve this question

edited Mar 8 at 8:14

Pᴇʜ

25.1k63052

asked Mar 8 at 2:17

SasSas

1

share|improve this question

edited Mar 8 at 8:14

Pᴇʜ

25.1k63052

asked Mar 8 at 2:17

SasSas

1

edited Mar 8 at 8:14

Pᴇʜ

25.1k63052

edited Mar 8 at 8:14

Pᴇʜ

25.1k63052

asked Mar 8 at 2:17

SasSas

1

asked Mar 8 at 2:17

SasSas

1

1 Answer
1

active

oldest

votes

1

Sas, if it has to be done in VBA, this would do the trick (tested):

Sub Arrays()

Dim ws As Worksheet
Dim MyRange As Range
Dim x As Integer
Dim arr_MyArray() As Integer

Set ws = Worksheets("Sheet1")
Set MyRange = ws.Range("A1", ws.Range("A10"))
ReDim arr_MyArray(1 To MyRange.Cells.Count)

For x = 1 To UBound(arr_MyArray)
 arr_MyArray(x) = Application.WorksheetFunction.Rank(x, MyRange, 1)
Next

End Sub

Come check out ExcelVBADude YouTube Channel!

share|improve this answer

answered Mar 8 at 3:39

J VBAJ VBA

16715

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  That's a strange way to set a range. Why not ws.Range("A1:A10")
  
  – urdearboy
  Mar 8 at 3:51
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, used to setting non-contiguous ranges. And, you can't spell strange without range! hehe
  
  – J VBA
  Mar 8 at 4:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  1 point for making me realize that range("a1", "a10") actually works and is equivalent to range("a1:" & "a10"), and that it is actually faster to provide 2 strings rather than concatenate them (tried in a 10000 loop)
  
  – Patrick Honorez
  Mar 8 at 8:32
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55055791%2fhow-to-insert-data-in-ascending-order-into-an-array-in-vba-excel%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Sas, if it has to be done in VBA, this would do the trick (tested):

Sub Arrays()

Dim ws As Worksheet
Dim MyRange As Range
Dim x As Integer
Dim arr_MyArray() As Integer

Set ws = Worksheets("Sheet1")
Set MyRange = ws.Range("A1", ws.Range("A10"))
ReDim arr_MyArray(1 To MyRange.Cells.Count)

For x = 1 To UBound(arr_MyArray)
 arr_MyArray(x) = Application.WorksheetFunction.Rank(x, MyRange, 1)
Next

End Sub

Come check out ExcelVBADude YouTube Channel!

share|improve this answer

answered Mar 8 at 3:39

J VBAJ VBA

16715

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  That's a strange way to set a range. Why not ws.Range("A1:A10")
  
  – urdearboy
  Mar 8 at 3:51
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, used to setting non-contiguous ranges. And, you can't spell strange without range! hehe
  
  – J VBA
  Mar 8 at 4:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  1 point for making me realize that range("a1", "a10") actually works and is equivalent to range("a1:" & "a10"), and that it is actually faster to provide 2 strings rather than concatenate them (tried in a 10000 loop)
  
  – Patrick Honorez
  Mar 8 at 8:32
  

  
  
  
  

add a comment | 

1

Sas, if it has to be done in VBA, this would do the trick (tested):

Sub Arrays()

Dim ws As Worksheet
Dim MyRange As Range
Dim x As Integer
Dim arr_MyArray() As Integer

Set ws = Worksheets("Sheet1")
Set MyRange = ws.Range("A1", ws.Range("A10"))
ReDim arr_MyArray(1 To MyRange.Cells.Count)

For x = 1 To UBound(arr_MyArray)
 arr_MyArray(x) = Application.WorksheetFunction.Rank(x, MyRange, 1)
Next

End Sub

Come check out ExcelVBADude YouTube Channel!

share|improve this answer

answered Mar 8 at 3:39

J VBAJ VBA

16715

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  That's a strange way to set a range. Why not ws.Range("A1:A10")
  
  – urdearboy
  Mar 8 at 3:51
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sorry, used to setting non-contiguous ranges. And, you can't spell strange without range! hehe
  
  – J VBA
  Mar 8 at 4:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  1 point for making me realize that range("a1", "a10") actually works and is equivalent to range("a1:" & "a10"), and that it is actually faster to provide 2 strings rather than concatenate them (tried in a 10000 loop)
  
  – Patrick Honorez
  Mar 8 at 8:32
  

  
  
  
  

add a comment | 

Sas, if it has to be done in VBA, this would do the trick (tested):

Sub Arrays()

Dim ws As Worksheet
Dim MyRange As Range
Dim x As Integer
Dim arr_MyArray() As Integer

Set ws = Worksheets("Sheet1")
Set MyRange = ws.Range("A1", ws.Range("A10"))
ReDim arr_MyArray(1 To MyRange.Cells.Count)

For x = 1 To UBound(arr_MyArray)
 arr_MyArray(x) = Application.WorksheetFunction.Rank(x, MyRange, 1)
Next

End Sub

Come check out ExcelVBADude YouTube Channel!

share|improve this answer

answered Mar 8 at 3:39

J VBAJ VBA

16715

Sub Arrays()

Dim ws As Worksheet
Dim MyRange As Range
Dim x As Integer
Dim arr_MyArray() As Integer

Set ws = Worksheets("Sheet1")
Set MyRange = ws.Range("A1", ws.Range("A10"))
ReDim arr_MyArray(1 To MyRange.Cells.Count)

For x = 1 To UBound(arr_MyArray)
 arr_MyArray(x) = Application.WorksheetFunction.Rank(x, MyRange, 1)
Next

End Sub

share|improve this answer

answered Mar 8 at 3:39

J VBAJ VBA

16715

answered Mar 8 at 3:39

J VBAJ VBA

16715

answered Mar 8 at 3:39

J VBAJ VBA

16715