+ Reply to Thread
Results 1 to 5 of 5

Create a number array in vba

Hybrid View

  1. #1
    Registered User
    Join Date
    09-15-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    35

    Create a number array in vba

    I have a range ("A1:A60") that contains numbers out 2 decimals and blanks.

    Examples of the numbers in the range are 1.01, 1.02, 2.01, 2.02, 1.03, 1.04, 2.03

    I'd like to do the following:

    1. grab these numbers and put them in an array (maybe array is the wrong term)
    2. add a "x.00" and "x.99" for every integer to the array.
    (So in the above example, the array would now also include 1.00, 1.99, 2.00 and 2.99)
    3. Sort the array Ascending
    4. Paste the new array into cell B1.

    Is this possible?

    Thanks,
    Ajm

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Create a number array in vba

    Maybe :
    Sub Test()
      Dim coll As New Collection, arr, i As Long, j As Long, v
      arr = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
      For i = 1 To UBound(arr, 1)
          If Not IsEmpty(arr(i, 1)) Then
             On Error Resume Next
                coll.Add key:=CStr(arr(i, 1)), Item:=arr(i, 1)
                v = Split(arr(i, 1), ".")(0)
                coll.Add key:=v, Item:=CSng(v)
                coll.Add key:=v & ".99", Item:=CSng(v & ".99")
             On Error GoTo 0
          End If
      Next i
      ReDim arr(1 To coll.Count, 1 To 1)
      i = 0
      For Each v In coll
          i = i + 1
          arr(i, 1) = v
      Next v
      For i = 1 To UBound(arr, 1)
          For j = i + 1 To UBound(arr, 1)
              If arr(j, 1) < arr(i, 1) Then
                 v = arr(i, 1)
                 arr(i, 1) = arr(j, 1)
                 arr(j, 1) = v
              End If
          Next j
      Next i
      With Range("B1").Resize(UBound(arr, 1), UBound(arr, 2))
        .Value = arr
        .NumberFormat = "0.00"
      End With
    End Sub
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Create a number array in vba

    Sub FillA1toA60()
      Dim i%, j%
      
      For i = 1 To 60 Step 2
        With Cells(i, "A")
          .Value = i + j + 0.01
          .Offset(1).Value = .Value + 0.01
        End With
        j = j - 1
      Next i
      
      [A59] = 0.01
      [A60] = 0.02
    End Sub
    
    Sub Main()
      Dim a, b, c, i%
      a = WorksheetFunction.Transpose(Range("A1:A60").Value)
      b = a
      For i = 1 To 60 Step 2
        b(i) = Int(a(i))
        b(i + 1) = Int(a(i + 1)) + 0.99
      Next i
      c = ArrayListSort(b)
      
      With [B1].Resize(60)
        .Value = WorksheetFunction.Transpose(c)
        .NumberFormat = "#.00"
      End With
    End Sub
    
    Function ArrayListSort(sn As Variant, Optional bAscending As Boolean = True)
      Dim cl
        With CreateObject("System.Collections.ArrayList")
            For Each cl In sn
                .Add cl
            Next
             
            .Sort 'Sort ascendending
            If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
            ArrayListSort = .Toarray()
        End With
    End Function

  4. #4
    Registered User
    Join Date
    09-15-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    35

    Re: Create a number array in vba

    @karedog, awesome! thanks a lot.

    I changed this:
    arr = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    to:
    arr = Sheet4.Range("D8:D68")
    otherwise the value of the last cell was being used.

    one last thing - i forgot to mention that the integers can skip e.g. 1.01, 1.02, 14.01, 14.02, 2.01 and also have #N/A's in between integers. any way to sort them ascending? and skip the #N/As to speed it up?

    @Kenneth, your code created a list.
    Last edited by ajm1991; 01-22-2017 at 04:27 AM.

  5. #5
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Create a number array in vba

    You are welcome, thanks for the rep.points.

    Please change the code to :
    'If Not IsEmpty(arr(i, 1)) Then
    If Not IsEmpty(arr(i, 1)) And IsNumeric(arr(i, 1)) Then
    If this is still giving unexpected result, you need to upload your workbook (data and expected result).
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Loop through list, add to Array, count number of occurrences, clear array
    By HalPlz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-11-2016, 09:42 PM
  2. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  3. Replies: 6
    Last Post: 09-25-2013, 10:08 PM
  4. Create an array based off values in another array - exclude blanks
    By clifton1230 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2013, 05:35 PM
  5. [SOLVED] Create 1-col array identifying occurrence count in a 1-col array
    By empsall in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2013, 12:50 PM
  6. Replies: 0
    Last Post: 10-13-2012, 10:13 PM
  7. Create a random array contain a fixed number
    By nguyennb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2009, 05:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1