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

[mikeoly]

Hi All,
I'm running into a Type Mismatch error that's driving me bonkers :doh:-- 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\ (file://\\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?