Segregate data by data types

[YasserKhalil]

Hello everyone

I have some data (mixed data types in column A). How can I split each data type into another column?
I mean numbers to be in column, string in column, dates in column and so on
This is my try till now but I didn't get all the results as expected

Code: Select all

Sub Test()
    Dim a, b(), dic As Object, i As Long, k As Long, ii As Long, n As Long
    a = Range("A1:A10").Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(a) To UBound(a)
        If Not dic.Exists(VarType(a(i, 1))) Then
            dic.Item(VarType(a(i, 1))) = Empty
            ReDim Preserve b(UBound(a, 1), k)
            k = k + 1
        End If
        n = 0
        Do Until b(i - 1, k - 1) <> Empty
            b(i - 1, k - 1) = a(i, 1)
        Loop
    Next i
    Range("J1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

Untitled.png

[HansV]

For example:

Code: Select all

Sub Test()
    Dim a() As Variant
    Dim b() As Variant
    Dim i As Long
    Dim n As Long
    Dim c As Long
    a = Range("A1:A10").Value
    n = UBound(a, 1)
    ReDim b(1 To n, 1 To 6)
    For i = 1 To n
        Select Case VarType(a(i, 1))
            Case vbEmpty
                c = 1
            Case vbString
                c = 2
            Case vbDouble ' all numbers in Excel are of type Double
                c = 3
            Case vbDate
                c = 4
            Case vbBoolean
                c = 5
            Case vbError
                c = 6
        End Select
        b(i, c) = a(i, 1)
    Next i
    Range("J1").Resize(n, 6).Value = b
End Sub

[YasserKhalil]

Thank you very much, my tutor. The code is very good but I need to modify the code I started so as to make it more dynamic as for the data types, and also to learn the trick of how to populate data according to the dynamic arrays

[HansV]

As far as I know, those 6 are the only data types of cell values in Excel.
You can change A1:A10 to a larger range (in one column); the code will still work.

[YasserKhalil]

There are about 23 data types according to the following link
https://www.wallstreetmojo.com/vba-vartype/

[HansV]

Yes, I know that! But those are the data types available in VBA. Most of those are not used by Excel.
For example, all numbers in a cell are of type vbDouble - not vbByte, vbInteger, vbLong, vbLongLong, vbSingle or vbDecimal.
Neither can a cell value be of type vbObject, for example.
But I admit that I missed one: vbCurrency.

Code: Select all

Sub Test()
    Dim a() As Variant
    Dim b() As Variant
    Dim i As Long
    Dim n As Long
    Dim c As Long
    a = Range("A1:A10").Value
    n = UBound(a, 1)
    ReDim b(1 To n, 1 To 7)
    For i = 1 To n
        Select Case VarType(a(i, 1))
            Case vbEmpty
                c = 1
            Case vbString
                c = 2
            Case vbDouble ' all numbers in Excel are of type Double
                c = 3
            Case vbCurrency
                c = 4
            Case vbDate
                c = 5
            Case vbBoolean
                c = 6
            Case vbError
                c = 7
        End Select
        b(i, c) = a(i, 1)
    Next i
    Range("J1").Resize(n, 7).Value = b
End Sub

[YasserKhalil]

Thank you very much, my tutor.
Best and Kind Regards