Results 1 to 3 of 3

Concatenate Function getting Type mismatch error

Threaded View

capson Concatenate Function getting... 12-08-2015, 12:59 PM
AlphaFrog Re: Concatenate Function... 12-08-2015, 01:48 PM
capson Re: Concatenate Function... 12-08-2015, 05:59 PM
  1. #1
    Forum Contributor
    Join Date
    07-11-2010
    Location
    Minneapolis, USA
    MS-Off Ver
    Excel 2016
    Posts
    308

    Concatenate Function getting Type mismatch error

    I am trying to concatenate columns with the following code (I know there are many ways to do this but am trying to follow through on this idea) but it is not working, I get "Type mismatch" here:
    sTemp(x) = .Transpose(.Transpose(Intersect(UNR, UNR(i, 1).EntireRow).Value2))
    Thanks

    Sub CCat()
    Dim r1 As Range
    
        Set r1 = Range("A1:B1, G1:G1, I1:I1")
    
        ConcatA "Elements", r1
        
    End Sub
    Function ConcatA(shtName As String, rng As Range) As String
    Dim ws as worksheet
    Dim UNR As Range
    Dim sTemp() As String
    Dim x As Long, i As Long
        
        
        Set ws = ThisWorkbook.Sheets(shtName)
        
        If rng Is Nothing Then Exit Function
        With ws
             Set UNR = Intersect(.UsedRange, rng.EntireColumn)
        End With
    
     For i = 2 To UBound(UNR.Value2)
            x = x + 1
            ReDim Preserve sTemp(1 To x)
            With Application
                   sTemp(x) = .Transpose(.Transpose(Intersect(UNR, UNR(i, 1).EntireRow).Value2))
            End With
     Next i
    
     Concat = Join(sTemp, ";")
    
    'Post back to last column + 1
     With ws.UsedRange.Offset(, UBound(UNR.Value2) + 1).Resize(, 1)
            .Value = ConcatA
     End With
    
    End Function
    Last edited by capson; 12-08-2015 at 01:01 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Concatenate macro Type Mismatch error
    By rdowney79 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-12-2014, 02:05 PM
  2. Instr() function giving error: "Type mismatch"
    By arjun.majumdar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-22-2013, 04:17 AM
  3. VBA Function Generating Type Mismatch Error
    By Mediterranean in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-06-2013, 11:22 AM
  4. [SOLVED] 'Type mismatch error' while translating the worksheet function to VBA
    By ramananhrm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-30-2013, 03:02 AM
  5. Replies: 4
    Last Post: 03-27-2013, 12:16 PM
  6. Date array function has type mismatch error...
    By secret2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-21-2011, 02:07 PM
  7. Range and Find function error: Type Mismatch
    By VBNewb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2011, 02:10 PM
  8. Help: Compile error: type mismatch: array or user defined type expected
    By lvcha.gouqizi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2005, 05:05 PM

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