locked
VBA Excel - Values assigned to Array are stored as numbers, not as text RRS feed

  • Question

  • I'm populating an array from several columns. In one column there are sometimes numbers, letters or a combination of it. The numbers in the source are formatted as text. Meaning I have leading zero's. When the array is populated, it converts the text to numbers. This means I lose my leading zero's if applicable. But I need this.

    What did I tried to so far:

    1. formatting the sheet where the array will post it to (will not work as the values in the array are already as numbers and lost the leading zero's);
    2. Changed Dim myArray() as Variant to Dim myArray() as String;
    3. Changed Dim i, j, k as Long to Dim i, j, k as String;
    4. use Cstr to store the data in the array as a string;

    This is my code what I have now:

    numberOfRecords = Range(Selection, Selection.End(xlDown)).Rows.Count + 1
    
    Dim myArray() As String
    Dim i, j, k As Long
    k = 0
    ReDim myArray(numberOfRecords, 2 To 6) As String
    
    For i = 2 To numberOfRecords
        If IsError(Application.Match(Cells(i, "A").Value, 
            Sheets("SE16N").Range("A:A"), 0)) Then
                For j = 2 To 6
                    myArray(k, j) = CStr(Cells(i, j).Text)
                    Debug.Print myArray(k, j) 'Just to check how the array is storing data
                Next j
            k = k + 1
        End If
    Next i

    I hope someone can help me.

    Thursday, October 19, 2017 2:46 PM

All replies

  • Are you sure that the format of the cell is text? If it is number then you won't have the leading zeros before you even get it.


    Sam Hobbs
    SimpleSamples.Info

    Thursday, October 19, 2017 5:48 PM
  • The numbers in the source are formatted as text. Meaning I have leading zero's. When the array is populated, it converts the text to numbers.

    Use the Range object to read the values into an array directly.

    That creates a 2-dimensional array. If it is really necessary you can use WorksheetFunction.Transpose to transpose it into a 1-dimensional array.

    Andreas.

    Sub Test()
      Dim Arr
      Arr = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    End Sub


    Friday, October 20, 2017 10:11 AM
  • There must be a different error in your code. The following code, with 002 in A1 of the active sheet produces 002 in the Immediate Window:

    Dim arr(1 to 1)

    Set rng1 = Range("a1")

    arr(1) = rng1.Value

    Debug.Print arr(1)

    Monday, October 30, 2017 6:57 AM