Type Mismatch error

[dwrowe001]

Hi,
I have 3 three columns of numbers, the first column starts at I4, second starts at L4 and the third starts at O4. the numbers could go as high as 60. these columns have spaces, gaps in them... so I have an array formula in columns J M and P which removes the spaces or gaps..

Im trying to combine the three columns of numbers (J4:J63, M4:M63 and P4:63) into one column starting at T4. I have a macro to do this and have it assigned to a button named combine. when I click on the button I get a "Type Mismatch" error..

I have attached a file with what I have.

I can't figure out why I'm getting this error... I need help and come to you.

Thank you in advance.

 

[Marc L]

Hi !

First way upon array formula columns J, M & P :​

Sub Demo1()
    Dim R&, C%, V
        [T4].CurrentRegion.Clear
        R = 4
    For C = 10 To 16 Step 3
        V = Cells(4, C).Resize(60).Value
        Cells(R, 20).Resize(60).Value = V
        R = Cells(Rows.Count, 20).End(xlUp)(2).Row
    Next
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Second way without array formula columns
just directly upon data columns I, L & O :​

Sub Demo2()
    Dim R&, C%
        Application.ScreenUpdating = False
        [T4].CurrentRegion.Clear
        R = 4
    For C = 9 To 15 Step 3
        Range(Cells(4, C), Cells(Rows.Count, C).End(xlUp)).Copy Cells(R, 20)
        R = Cells(Rows.Count, 20).End(xlUp)(2).Row
    Next
    With Range("T4:U" & R - 1)
        .Columns(2).Formula = "=T4="""""
        .Sort .Cells(2), xlAscending, Header:=xlNo
        .Columns(2).Clear
    End With
        Application.ScreenUpdating = True
End Sub

You may Like it !
​

 

[Marc L]

Third way directly upon data columns I, L & O
reading each cell of a global range like in your code :​

Sub Demo3()
             Dim Rg As Range, R&, V(1 To 180, 0)
    With Sheet13
            .[T4].CurrentRegion.Clear
        For Each Rg In .[I4:I63,L4:L63,O4:O63]
              If Rg.Value > 0 Then R = R + 1: V(R, 0) = Rg.Value
        Next
            .[T4].Resize(R).Value = V
    End With
End Sub

You should Like it !
​

 

[Marc L]

Your error raises 'cause of your superfluous CLng statement …​

 

[dwrowe001]

Hi Marc,
Thank you for the 3 macro examples to try.. I should have been more precise as to what I needed... rather then combining the 3 columns consecutively, one column after then other, I need them combined numerically, from smallest to highest numbers, 1 to 60, only once, excluding duplicates. I updated my example spreadsheet and uploaded it so you can see what i'm talking about. I'm sorry for this confusion, it's my fault for not expressing what I need in more detail.
I appreciate your all your effort to fix my problem.
Thank you.
Dave

 

[Marc L]

According to the last explanation & attachment
(no needs formula columns J, M & P) :​

Sub Demo4()
             Dim Rg As Range
            [T4].CurrentRegion.ClearContents
    With CreateObject("System.Collections.ArrayList")
        For Each Rg In [I4:I63,L4:L63,O4:O63]
            If Not .Contains(Rg.Value) Then .Add Rg.Value
        Next
           .Sort
            [T4].Offset(.Contains(Empty)).Resize(.Count).Value = Application.Transpose(.ToArray)
           .Clear
    End With
End Sub

You could Like it !
​

 

[dwrowe001]

Great, that works!! Thank you Marc L for your help solving my issue, really appreciate it.

Dave.