Run Time Error 13: Type Mismatch (only 4 lines of code to look at)

[Chris Macro]

I am getting this error on the 3rd line of my code (MyVariable = Join.....). Any ideas on what could be causing this?

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#00007F">Dim</SPAN> Lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MasterEmailList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, MyVariable <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><br><SPAN style="color:#007F00">'Combine Mill Email Groups into One Large String</SPAN><br>        Lastrow = sheet4.Cells(Rows.Count, "J").End(xlUp).Row<br>        <br>        MasterEmailList = sheet4.Range("J1:J" & Lastrow)<br>        MyVariable = Join(Application.Transpose(MasterEmailList), "; ")<br>        sheet4.Cells(Lastrow + 1, "J") = MyVariable<br>        <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Norie]

Is there anything in column J on the worksheet with codename Sheet4?

 

[Jonmo1]

Also, any error values like #N/A within column J will cause the type mismatch.

 

[Chris Macro]

Nope, all there is in Column J is data inside the range of J1:J7. The Line that is running the error is when I am trying to Join the range into a one line string.

 

[Chris Macro]

Ok, so when I modify the code to look like this (below), then it works.....but I need it to be pulling from sheet4.

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#00007F">Dim</SPAN> Lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MasterEmailList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, MyVariable <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><br><SPAN style="color:#007F00">'Combine Mill Email Groups into One Large String</SPAN><br>        Lastrow = Sheet5.Cells(Rows.Count, "J").End(xlUp).Row<br>        <br>        MasterEmailList = Sheet5.Range("J1:J" & Lastrow)<br>        MyVariable = Join(Application.Transpose(MasterEmailList), "; ")<br>        sheet4.Cells(Lastrow + 1, "J") = MyVariable<br>        <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Jonmo1]

Are you sure the CodeName of the sheet is Sheet4?
This is not necessarily the same as the Name as you see it on the Sheet's Tab.

Try
Sheets("Sheet4") Instead.


also, just for troubleshooting, hard code the range without the lastrow variable

MasterEmailList = sheet4.Range("J1:J7")

 

[Chris Macro]

My sheet name is actually called "Email List". So using "sheet4" should not be the issue. I hard coded the range and it still gave me the same error. I think the problem is the line stating "MasterEmailList = sheet4.Range("J1:J" & Lastrow)". When I change the sheet name to 5, it capitalizes the "s" and makes it say "Sheet5". Is there a reason why it automatically makes sheet4 lowercase?

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br><SPAN style="color:#00007F">Dim</SPAN> Lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> MasterEmailList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, MyVariable <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><br><SPAN style="color:#007F00">'Combine Mill Email Groups into One Large String</SPAN><br>        Lastrow = sheet4.Cells(Rows.Count, "J").End(xlUp).Row<br>        <br>        MasterEmailList = Sheet5.Range("J1:J" & Lastrow)<br>        MyVariable = Join(Application.Transpose(MasterEmailList), "; ")<br>        sheet4.Cells(Lastrow + 1, "J") = MyVariable<br>        <br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Jonmo1]

My sheet name is actually called "Email List".

Does this work then..

Sub Macro1()
Dim Lastrow As Long
Dim MasterEmailList As Variant, MyVariable As String
Dim ws As Worksheet

Set ws = Sheets("Email List")


'Combine Mill Email Groups into One Large String
        Lastrow = ws.Cells(Rows.Count, "J").End(xlUp).Row
        
        MasterEmailList = ws.Range("J1:J" & Lastrow)
        MyVariable = Join(Application.Transpose(MasterEmailList), "; ")
        ws.Cells(Lastrow + 1, "J") = MyVariable
        

End Sub

 

[Chris Macro]

That didn't work either. But I think I might know why I am getting the error. Right now inside the range, the cells have a really long string. I think when I am trying to join the strings, the size might be too big??? when I just put 1,2,3,4, etc... inside the range the code worked. Is there a way to fix this???

 

[Jonmo1]

there is a limit to the length of a string...
I'm not sure off hand what it is, but I'm sure it's quite large probably at least 32k

How long are the strings you're joining?
Or more precisely, what would the lenght of the resulting string?
Do an =LEN(J1) on each of the 7 cells, and Sum them * 2
*2 for the ; delimiter