Run-time error '13': Type Mismatch -- HELP

[mikeoly]

Hi All,
I'm running into a Type Mismatch error that's driving me bonkers -- line 11 ("wline=wline & Join....). I realize that this may not be the cleanest code as well, so feel free to offer any additional comments/suggestions.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Msg As String, Ans As Variant
    Dim wline As Variant
    Dim r As Integer
    Dim lcol As Integer
    Dim strDir As String
    
    If Sheets("IBNRPack").OLEObjects("CheckBox1").Object.Value = True Then
    
        wline = ""
        With Worksheets("Output")
        For r = 5 To .Cells(.Rows.Count, "A").End(xlUp).Row
            lcol = .Cells(r, 256).End(xlToLeft).Column
            wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine
        Next r
        End With
        
         
        
        DataAsOf = Sheets("Output").Range("A2").Value
        
        strDir = Trim(\\yadayada\Output\"]\\yadayada\Output\"]\\yadayada\Output\& DataAsOf)
        If Dir(strDir, vbDirectory) = NullString Then
            MkDir (strDir)
        Else: End If
        
        
        Open "\\yadayada\Output\" & DataAsOf & "\" & Sheets("Output").Range("C5").Value & " " & DataAsOf & " (" & Sheets("Output").Range("E5").Value & ")" & ".csv" For Output As #1 'Replaces existing file
        Print #1, wline
        Close #1
    Else
     Exit Sub
    End If
    
    
Quit:
End Sub

Thank you so much!

[p45cal]

breakdown and debug with the likes of:

  For r = 5 To .Cells(.Rows.Count, "A").End(xlUp).Row
    lcol = .Cells(r, 256).End(xlToLeft).Column
    Application.Goto .Range("A" & r).Resize(, lcol)
    x = Application.Transpose(.Range("A" & r).Resize(, lcol))
    x = Application.Transpose(x)
    x = Join(x, ",")  'bombs out here when only one cell.
    wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine
  Next r

then when you find out what's wrong you may be doing the likes of:

  For r = 5 To .Cells(.Rows.Count, "A").End(xlUp).Row
    lcol = .Cells(r, 256).End(xlToLeft).Column
    x = Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol)))
    If IsArray(x) Then x = Join(x, ",")
    wline = wline & x & vbNewLine
  Next r

[mikeoly]

Can you expand on what your comment "bombs out here when only one cell" means please?

I continue to run into the mismatch error. I thought it may be the fact that I format some of the cells as text within the spreadsheet, but after removing all of those I still run into the error. Any other ideas?

[UPDATE]: I found that some of the cells were pulling #VALUE errors from other spreadsheet, which was driving the mismatch errors. Thanks for your help p45cal!!

[p45cal]

Can you expand on what your comment "bombs out here when only one cell" means please?

If .Range("A" & r).Resize(, lcol) is a single cell then the Join statement will struggle; it expects to see a single dimension array, as a doubly-transposed range 2 or more cells across and 1 row deep will be.

[UPDATE]: I found that some of the cells were pulling #VALUE errors from other spreadsheet, which was driving the mismatch errors.

So what did you do to resolve?