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

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
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>
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Is there anything in column J on the worksheet with codename Sheet4?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Also, any error values like #N/A within column J will cause the type mismatch.
 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
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

Well-known Member
Joined
Nov 2, 2011
Messages
1,341

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Nov 2, 2011
Messages
1,341

ADVERTISEMENT

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>
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
My sheet name is actually called "Email List".

Does this work then..


Rich (BB code):
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

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top