Ggtcf

Why is it that I can sometimes guess the next note?

Need help understanding what a natural log transformation is actually doing and why specific transformations are required for linear regression

What if a revenant (monster) gains fire resistance?

How do you make your own symbol when Detexify fails?

Using substitution ciphers to generate new alphabets in a novel

What is Cash Advance APR?

Why does AES have exactly 10 rounds for a 128-bit key, 12 for 192 bits and 14 for a 256-bit key size?

Why should universal income be universal?

Are Captain Marvel's powers affected by Thanos' actions in Infinity War

How to fade a semiplane defined by line?

What are some good ways to treat frozen vegetables such that they behave like fresh vegetables when stir frying them?

Is there an injective, monotonically increasing, strictly concave function from the reals, to the reals?

Mimic lecturing on blackboard, facing audience

Angel of Condemnation - Exile creature with second ability

Creepy dinosaur pc game identification

How to explain what's wrong with this application of the chain rule?

Fear of getting stuck on one programming language / technology that is not used in my country

Quoting Keynes in a lecture

Is there a way to get `mathscr' with lower case letters in pdfLaTeX?

Is there a RAID 0 Equivalent for RAM?

Recommended PCB layout understanding - ADM2572 datasheet

putting logo on same line but after title, latex

Picking the different solutions to the time independent Schrodinger eqaution

Keeping a ball lost forever

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

-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ᴇʜ

24.4k63052

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ᴇʜ

24.4k63052

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ᴇʜ

24.4k63052

asked Mar 8 at 2:17

SasSas

1

share|improve this question

edited Mar 8 at 8:14

Pᴇʜ

24.4k63052

asked Mar 8 at 2:17

SasSas

1

share|improve this question

edited Mar 8 at 8:14

Pᴇʜ

24.4k63052

asked Mar 8 at 2:17

SasSas

1

edited Mar 8 at 8:14

Pᴇʜ

24.4k63052

edited Mar 8 at 8:14

Pᴇʜ

24.4k63052

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