VBA Type Mismatch Error – The Ultimate Guide


VBA Type Mismatch Explained

A VBA Type Mismatch Error occurs when you try to assign a value between two different variable types.

The error appears as “run-time error 13 – Type mismatch”.

VBA Type Mismatch Error 13


For example, if you try to place text in a Long integer variable or you try to place text in a Date variable.

Let’s look at a concrete example. Imagine we have a variable called Total which is a Long integer.


If we try to place text in the variable we will get the VBA Type mismatch error(i.e. VBA Error 13).

' https://excelmacromastery.com/
Sub TypeMismatchString()

    ' Declare a variable of type long integer
    Dim total As Long
    ' Assigning a string will cause a type mismatch error
    total = "John"
End Sub


Let’s look at another example. This time we have a variable ReportDate of type Date.

If we try to place a non-date in this variable we will get a VBA Type mismatch error

' https://excelmacromastery.com/
Sub TypeMismatchDate()

    ' Declare a variable of type Date
    Dim ReportDate As Date
    ' Assigning a number causes a type mismatch error
    ReportDate = "21-22"
End Sub


In general, VBA is very forgiving when you assign the wrong value type to a variable e.g.

Dim x As Long

' VBA will convert to integer 100
x = 99.66

' VBA will convert to integer 66
x = "66"


However, there are some conversions that VBA cannot do

Dim x As Long

' Type mismatch error
x = "66a"


A simple way to explain a VBA Type mismatch error, is that the items on either side of the equals evaluate to a different type.

When a Type mismatch error occurs it is often not as simple as these examples. For these more complex cases we can use the Debugging tools to help us resolve the error.


VBA Type Mismatch YouTube Video

Don’t forget to check out my YouTube video on the Type Mismatch Error here:


How to Locate the Type Mismatch Error

The most important thing to do when solving the Type Mismatch error is to, first of all, locate the line with the error and then locate the part of the line that is causing the error.

If your code has Error Handling then it may not be obvious which line has the error.

If the line of code is complex then it may not be obvious which part is causing the error.

The following video will show you how to find the exact piece of code that causes a VBA Error in under a minute:


The following sections show the different ways that the VBA Type Mismatch error can occur.

Assigning a string to a numeric

As we have seen, trying to place text in a numeric variable can lead to the VBA Type mismatch error.

Below are some examples that will cause the error

' https://excelmacromastery.com/
Sub TextErrors()

    ' Long is a long integer
    Dim l As Long
    l = "a"
    ' Double is a decimal number
    Dim d As Double
    d = "a"
    ' Currency is a 4 decimal place number
    Dim c As Currency
    c = "a"
    Dim d As Double
    ' Type mismatch if the cell contains text
    d = Range("A1").Value
End Sub


Invalid date

VBA is very flexible when it comes to assigning a date to a date variable. If you put the month in the wrong order or leave out the day, VBA will still do it’s best to accommodate you.

The following code examples show all the valid ways to assign a date followed by the cases that will cause a VBA Type mismatch error.

' https://excelmacromastery.com/
Sub DateMismatch()

    Dim curDate As Date
    ' VBA will do it's best for you
    ' - These are all valid
    curDate = "12/12/2016"
    curDate = "12-12-2016"
    curDate = #12/12/2016#
    curDate = "11/Aug/2016"
    curDate = "11/Augu/2016"
    curDate = "11/Augus/2016"
    curDate = "11/August/2016"
    curDate = "19/11/2016"
    curDate = "11/19/2016"
    curDate = "1/1"
    curDate = "1/2016"
    ' Type Mismatch
    curDate = "19/19/2016"
    curDate = "19/Au/2016"
    curDate = "19/Augusta/2016"
    curDate = "August"
    curDate = "Some Random Text"

End Sub


Cell Error

A subtle cause of the VBA Type Mismatch error is when you read from a cell that has an error e.g.

VBA Runtime Error

If you try to read from this cell you will get a type mismatch error

Dim sText As String

' Type Mismatch if the cell contains an error
sText = Sheet1.Range("A1").Value

To resolve this error you can check the cell using IsError as follows.

Dim sText As String
If IsError(Sheet1.Range("A1").Value) = False Then
    sText = Sheet1.Range("A1").Value
End If

However, checking all the cells for errors is not feasible and would make your code unwieldy. A better way is to check the sheet for errors first and if errors are found then inform the user.

You can use the following function to do this

Function CheckForErrors(rg As Range) As Long

    On Error Resume Next
    CheckForErrors = rg.SpecialCells(xlCellTypeFormulas, xlErrors).Count

End Function

The following is an example of using this code

' https://excelmacromastery.com/
Sub DoStuff()

    If CheckForErrors(Sheet1.Range("A1:Z1000")) > 0 Then
        MsgBox "There are errors on the worksheet. Please fix and run macro again."
        Exit Sub
    End If
    ' Continue here if no error

End Sub


Invalid Cell Data

As we saw, placing an incorrect value type in a variable causes the ‘VBA Type Mismatch’ error. A very common cause is when the value in a cell is not of the correct type.

A user could place text like ‘None’ in a number field not realizing that this will cause a Type mismatch error in the code.

VBA Error 13

If we read this data into a number variable then we will get a ‘VBA Type Mismatch’ error error.

Dim rg As Range
Set rg = Sheet1.Range("B2:B5")

Dim cell As Range, Amount As Long
For Each cell In rg
    ' Error when reaches cell with 'None' text
    Amount = cell.Value
Next rg

You can use the following function to check for non numeric cells before you use the data

Function CheckForTextCells(rg As Range) As Long

    ' Count numeric cells
    If rg.Count = rg.SpecialCells(xlCellTypeConstants, xlNumbers).Count Then
        CheckForTextCells = True
    End If
End Function

You can use it like this

' https://excelmacromastery.com/
Sub UseCells()

    If CheckForTextCells(Sheet1.Range("B2:B6").Value) = False Then
        MsgBox "One of the cells is not numeric. Please fix before running macro"
        Exit Sub
    End If
    ' Continue here if no error

End Sub


Module Name

If you use the Module name in your code this can cause the VBA Type mismatch to occur. However in this case the cause may not be obvious.

For example let’s say you have a Module called ‘Module1’. Running the following code would result in the VBA Type mismatch error.

' https://excelmacromastery.com/
Sub UseModuleName()
    ' Type Mismatch
    Debug.Print module1

End Sub

VBA Type Mismatch Module Name


Different Object Types

So far we have been looking mainly at variables. We normally refer to variables as basic data types.

They are used to store a single value in memory.

In VBA we also have objects which are more complex. Examples are the Workbook, Worksheet, Range and Chart objects.

If we are assigning one of these types we must ensure the item being assigned is the same kind of object. For Example

' https://excelmacromastery.com/
Sub UsingWorksheet()

    Dim wk As Worksheet
    ' Valid
    Set wk = ThisWorkbook.Worksheets(1)
    ' Type Mismatch error
    ' Left side is a worksheet - right side is a workbook
    Set wk = Workbooks(1)

End Sub


Sheets Collection

In VBA, the workbook object has two collections – Sheets and Worksheets. There is a very subtle difference

  1. Worksheets – the collection of worksheets in the Workbook
  2. Sheets – the collection of worksheets and chart sheets in the Workbook

A chart sheet is created when you move a chart to it’s own sheet by right-clicking on the chart and selecting Move.

If you read the Sheets collection using a Worksheet variable it will work fine if you don’t have a chart sheet in your workbook.

If you do have a chart sheet then you will get the VBA Type mismatch error.

In the following code, a Type mismatch error will appear on the Next sh line if the workbook contains a chart sheet.

' https://excelmacromastery.com/
Sub SheetsError()

    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        Debug.Print sh.Name
    Next sh

End Sub


Array and Range

You can assign a range to an array and vice versa. In fact this is a very fast way of reading through data.

' https://excelmacromastery.com/
Sub UseArray()

    Dim arr As Variant
    ' Assign the range to an array
    arr = Sheet1.Range("A1:B2").Value
    ' Print the value a row 1, column 1
    Debug.Print arr(1, 1)

End Sub


The problem occurs if your range has only one cell. In this case, VBA does not convert arr to an array.

If you try to use it as an array you will get the Type mismatch error

' https://excelmacromastery.com/
Sub UseArrayError()

    Dim arr As Variant
    ' Assign the range to an array
    arr = Sheet1.Range("A1").Value
    ' Type mismatch will occur here
    Debug.Print arr(1, 1)

End Sub


In this scenario, you can use the IsArray function to check if arr is an array

' https://excelmacromastery.com/
Sub UseArrayIf()

    Dim arr As Variant
    ' Assign the range to an array
    arr = Sheet1.Range("A1").Value
    ' Type mismatch will occur here
    If IsArray(arr) Then
        Debug.Print arr(1, 1)
        Debug.Print arr
    End If

End Sub



This concludes the post on the VBA Type mismatch error. If you have a mismatch error that isn’t covered then please let me know in the comments.


What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)


Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook


  1. In your Sub TextErrors() example to show type mismatch, you said that type currency is a 4-decimal number. I think it is a 2-decimal number with a $ prefix

  2. In one of the examples of type mismatch, there is a line
    Set rg = Sheet1.Range(“B2:B5”).Value
    I think this should be
    Set rg = Sheet1.Range(“B2:B5”)

  3. I’m trying to loop through the shapes on a slide.
    dim shp as shape
    for each shp in whateverslide.shapes <— this line gives me a type mismatch but the watcher says shp is type "shape" and whateverslide.shapes is type "object/shape"
    The slide exists and has lots of shapes on it.

      1. The macro is in excel. It opened an existing powerpoint presentation and is manipulating the slides/shapes.

        1. Hi Matt,

          That code looks fine at an initial glance. Without access to the actual code and workbook, it is difficult to say what the issue is.


          1. I changed “dim shp as shape” to “dim shp as object” and the type error went away. What’s the difference between “shape” and “object/shape”? Googling isn’t helping.

          2. When you write code in Excel you need to use an external library to access the Powerpoint code.

            You can access using early or late binding.

            Early binding means you add the library(e.g. Microsoft Powerpoint 16.0 Object Library) from tools reference. You can refer to shapes etc. as normal items.

            Late binding means you using CreateObject to access the library. In this case you must refer to variables in the library as objects. The reason is that VBA will decide at runtime, which type of object they will be.

  4. Hi Paul,
    I hope you can help me. We use VBA code in Dynamics GP. We have a currency field and we need to check if the field value is 0.
    The following code has been working for various currencies all this while and I can’t understand why it is now failing.

    Cash1 is a Currency field on a window.

    If CDbl(Cash1.Value) 0 Then —————– where Cash1.Value is $ xxxx.xx

    I hit a Type mismatch error on the line.

  5. Hi Paul,
    This article was really helpful, but I have a question regarding the error when calling af function. I call a function with:

    oldfilename = copyHistoricData(companyfol, (companyfol.name Like “TDC*”), _
    short_older_period, short_old_period, OLD_YEAR, OLDER_YEAR, wbName)

    oldfilename is defined as a string – the Watch window agrees. The problem is the function copyhistoricdata. The Watch window says integer, but the function is defined as string:

    Function copyHistoricData(ByVal companyfol As Folder, includeTDCSheet As Boolean, _
    short_older_period As String, short_old_period As String, OLD_YEAR As String, OLDER_YEAR As String, wbName As String) As String

    The code works with Excel 2016, but the error type mismatch appears in Excel 2010.

    Do you have any idea what the problem is? The code has been used for some years and have worked without any problems before.

    Thanks in advance,


  6. I have built my modules in Microsoft Excel 2016 ’32bit’ version. And when I run the code in my friend’s system, Microsoft Excel 2016 ’64bit’ version, it shows type mismatch error.

    My code works fine on all systems but failed in 64bit system. Then I came across to the article, where the author has said, there are few changes for 32bit and 64bit in the declaration of variables.

    Like, during the calling windows API’s through functions, we need to put “ptrsafe” right before the declaration.

    And I found one more thing in the 64bit system, declaration of the variable with datatype LONG will create type mismatched too. How, because in VB7 or 64bit version, LONG is referred as ‘LONGLONG’ and my whole coding the few variables are defined as the LONG instead of LONGLONG.

    So, am I correct? are those things generating an error?

    1. Hi Kamal,

      The only change you need to make is to use the PtrSafe for 64-bit versions.

      LongLong is how the 64 bit version stores long but you just declare long and VBA will look after the storage.

      Type Mismatch in mostly caused by trying to place a string value in a numeric type.
      If you try to put a different numeric type in a variable then VBA will automatically convert this in most cases.

      Check the value and type of variable in the watch window and you should see the incompatibility between the types you have.

      1. Hi Paul,

        Thanks for the reply and I’ll surely check my code using watch windows.

        Thanks again.

  7. I am running a loop which deducts dates from one an other. However, this is leading to a type mismatch error.
    The Code is as follows:
    Sub SlTm()
    Dim i
    i = 1
    For i = 1 To 500 Step 1
    If Worksheets(“Listing Data”).Cells(i, 9).Value = 0 Then
    Worksheets(“Listing Data”).Cells(i, 14).ClearContents
    ElseIf Worksheets(“Listing Data”).Cells(i, 13).Value = 0 Then
    Worksheets(“Listing Data”).Cells(i, 14).ClearContents
    Worksheets(“Listing Data”).Cells(i, 14).Value = Worksheets(“Listing Data”).Cells(i, 13).Value – Worksheets(“Listing Data”).Cells(i, 9).Value
    End If
    Next i

    End Sub

    I am lost. 😀

    1. When the error appears, click on Debug.

      Open the Watch Window(View->Watch Window)

      Highlight and drag each of the following pieces of code to the watch window.

      Worksheets(“Listing Data”).Cells(i, 13).Value
      Worksheets(“Listing Data”).Cells(i, 9).Value

      You will see that one has a non-numeric value.

  8. Hi Paul,

    in a project on which I’m currently working, I have a Type Mismatch error which is not included in your listing. It is just “Type Mismatch” without any error number and the header of error message window says it is coming from Visual basic for applications. When this error occurs, the debugger will not detect it. It won’t stop on any line of code. When I press the OK button in the message window, it disappears and everything is working as expected.

    I was able to detect one procedure where this error occurs when this procedure is called. But the procedure is running from start to the end as expected, just the type mismatch error occurs during executing the code without stopping debugger on the line where the type mismatch error would be coming from. When I try to debug the procedure step by step, the type mismatch error do not occur.

    Any ideas how to track the source of this type mismatch error? This error occurs in Office 365 (latest release to date) as well as in Office 2010.

    Thanks in advance for any suggestions.

    1. Hi Jan,

      It sounds like a tricky one. Without seeing the code it is hard to tell.

      If you add Debug.Print statements to the code it will help you track it down. When you find the line where the error occurs it should make it easier to see what the problem is.


  9. Hi Paul

    I have the following simple vba code:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Sheets(“Savings (2)”).Range(“A1”).Value “” Then
    Sheets(“Savings (2)”).Range(“A1”).Value = “”
    Exit Sub
    End If

    If Sheets(“Savings (2)”).Range(“D6:D20”).Value “” And Sheets(“Savings (2)”).Range(“E6:U20”).Value = “” Then
    Cancel = True
    MsgBox “Please fill in all cells.”
    End If
    End Sub

    The mismatch error pops up when I put in this code and highlights the 2nd if statement. I need the code to work on a range of data instead of just one cell. I notice that if I change the range to just one particular cell i.e “D6” instead of “D6:D20” or “E6” instead of “E6:U20”, the code works. Can you advise on what I’m doing wrong here?


    1. Hi Liyana,

      Range(“A1”).Value is a single value.
      Range(“A1:A2”) is an array.

      You can assign between arrays: Range(“A1:A2”).Value = Range(“D1:D2”).Value
      But you cannot copy an array to a single value. You need to read through the array and compare each element.


  10. Private Sub CommandButton1_Click()

    CommandButton1.Enabled = True

    Dim myLookupValue As String
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    Dim myColumnIndex As Long
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myVLookupResult As Long

    Dim myTableArray As Range
    Dim arr As Variant

    myLookupValue = “050/2408180”
    myFirstColumn = 1
    myLastColumn = 3
    myColumnIndex = 2
    myFirstRow = 2
    myLastRow = 1048576

    With Worksheets(“Customers Sheet”)
    Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
    End With

    myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)

    MsgBox (myVLookupResult)
    End Sub

  11. This ‘ cell.Value < 0 Then ' will return an error type 13:

    For Each cell In Range("S2:S30")
    If cell.Value < 0 Then MsgBox ("Warning: Negative value!."), , "Invalid Entry"
    Next cell

    I don't know why.
    cell.value is type Variant/Empty
    0 is type Empty

    Many thanks

    1. You have to write the For loop like this: For Each cell In Range(“S2:S30”).Cells

      This is because cell is a Range, and Range(“S2:S30”) is a Range, just one Range, so as written “cell.Value” is an array, and that causes your error.

      Cells is a collection if any range’s individual cells, so that’s what you need to check every cell.

  12. I have a large table that I’m creating a macro in excel to find and replace the in rest of my workbook.
    Dim sht As Worksheet
    Dim fndList As Integer
    Dim rplcList As Integer
    Dim tbl As ListObject
    Dim myArray As Variant

    ‘Create variable to point to your table
    Set tbl = Worksheets(“Fields”).ListObjects(“FieldTable”)

    ‘Create an Array out of the Table’s Data
    Set TempArray = tbl.DataBodyRange
    myArray = Application.Transpose(TempArray)

    ‘Designate Columns for Find/Replace data
    fndList = 1
    rplcList = 2

    ‘Loop through each item in Array lists
    For x = LBound(myArray, 1) To UBound(myArray, 2)
    ‘Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
    For Each sht In ActiveWorkbook.Worksheets
    If sht.Name tbl.Parent.Name Then

    sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    End If
    Next sht
    Next x

    The error happens in the loop after the If statement (below is the highlighted portion when I debug). I believe the issue is the type of the x field, as when I debug it shows as Variant/Long but I’m not sure how to fix it. The values being updated are string fields a combo of alpha and numeric in each field.

    sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

  13. Hi there. I am getting a type mismatch error from the following line:
    Lat = (CDbl(LongLat2(1)) – CDbl(LongLat1(1))) * km_Lat
    Lat, km_Lat is defined as a double and LongLat1,2() as strings.
    It seems the CDbl function is not returning a double as when I watch the right hand side of the equation, it returns a integer value.
    Could you possibly assist on this?

    1. When the error occurs, pause the code and place each part of the line in the Watch Window. This will help you isolate the error.

      Most likely is that something contains text characters.

  14. Hi there.
    I have a Long variant that reads from InputBox function. When the user clicks “OK” without enter a number, I get a type missmatch error. How I can debug this bug?
    Thank you.

    1. set the Type parameter to 1 to make them select a number.

      Otherwise, have the return variable as a variant and check the type when it is returned.

  15. Hi There
    I am getting a typemismatch error on the following line.
    Cells(emptyRow, 4).Value = CDate(Date1.Value)
    I know its because the text box in my user form is blank but cant figure out how to code IF IS NULL value.

    1. Hi Dave,

      You can use the IsDate function to check if the textbox has a valid date.

      If IsDate(Date1.Value) = True Then
      Cells(emptyRow, 4).Value = Date1.Value
      End If


  16. At the top of this article you give two simple examples of what causes Type Mismatch: “For example, if you try to place text in a Long integer variable or you try to place a number in a Date variable”

    In fact a Date variable can receive any number, whole or decimal.

    1. Hi Andy,

      It should be “try to place text in a Date”. I have updated the post. Thanks for the feedback.

  17. I was wondering if you could help. I have 2 named ranges (on single cells) that I read in using the following code:

    facStages = CInt(mainSh.Range(“FactoryStages”).Value)
    siteStages = CInt(mainSh.Range(“SiteStages”).Value)

    When I run the macro, one has a type of Integer and the other has Variant/Integer, which means I get an error when I need to pass that value to a function that takes an Integer. I wondered if it was to do with the fact that I was passing it to a function, but when I switch which variable I pass to the function, the type didn’t change.

    I have a similar and possibly related issue where Worksheets that I’ve declared in an identical manner have either Worksheet/Sheet1 or Variant/Object/Sheet2 as the types at runtime and I don’t have a clue why it would be different.

    I’m completely bemused, if you have any idea I would be extremely grateful.

  18. Hi Paul,
    I’m getting Type Mismatch error 13 but relating to LBOUND and UBOUND. I have reviewed the entire code but couldn’t figure out where the error lies. Any help would be greatly appreciated!
    For J = LBound(Desc, 1) To UBound(Desc, 1)

  19. Paul,
    Working on someone else’s spreadsheet who set the format of a column as special: Zip Code. I get a mismatch when I do a lookup based on this value. I enter the data in a text box, then defined it as String and I get the error. Dim txtZipCode As String. I’ve tried Variant, Long & Double. Not sure what else to try.

  20. Hi Paul,

    I want to display a msgbox when a cell in col C changes to the text “Rights Exercise”. It works fine. I can even delete that cell and the macro will work. But when I delete/add a row it gives me the type mismatch error and then it no longer works unless I close excel completely.

    Is there a way I can get it to ignore deleting/adding a row to avoid the type mismatch error?

    Here’s the code:
    Private Sub Worksheet_Change(ByVal Target As Range) ‘Excel VBA with more cells in the range.

    If Not Intersect(Target, Range(“C2:C500”)) Is Nothing Then
    Application.EnableEvents = False
    If Target.Value = “Rights Exercise” Then
    MsgBox “hi”
    End If
    End If
    Application.EnableEvents = True
    End Sub

    Thanks for your help,

  21. Hi Paul,

    Figured out my issue. I needed to move the line (If Target.Cells.Count > 1 Then Exit Sub) above all other code. Now if anything row is added or deleted it exits right away and doesn’t cause the error.


      1. Hello,

        I have used the below code to try to make a filed to be mandatory. If that required filed is empty you won`t be able to save and close the file.

        What I want to do is to increase the Range from B6 to B100. But everytime i have the mismatch error 13.

        Could you please help? It is the first time when I am using VBA so please consider me a rookie

        Below is the code that I am using and it is working. But if I change the Range from (“B6:B100”) I will get the error 13:

        Private Sub Workbook_BeforeClose(Cancel As Boolean)
        ‘Step 1: Check to see if Cell B1 is blank
        If Sheets(“Samples_List”).Range(“B6”).Value = “” Then

        ‘Step 2: Blank: cancel the Close and tell the user
        Cancel = True
        MsgBox “Cell B can not be blank”
        ‘Step 3: Not Blank; Save and Close
        ActiveWorkbook.Close SaveChanges:=True
        End If
        End Sub

  22. In your experience, have you encountered any differences in Excel settings between UK/US users and European users (particularly in France) that would almost consistently prompt a French user to get Type Mismatch when using a workbook with VBA?

  23. Quick question for you.

    I have a function below in ThisWorkbook module

    Public Sub update_duties(day As Long)
    Dim c As Range
    Dim i As Long: i = 1
    For Each c In Range(daily_ranges(day – 1))
    If IdentifyDutyType(c.Value) “== Not Matched ==” Then
    duties(day, i) = c.Parent.Name & “!” & c.Address(External:=False)
    i = i + 1
    End If
    Next c
    End Sub

    When I call it say from another sheets code using the following code I get a type mismatch.

    Call ThisWorkbook.update_atco_duties(1)

    Am I missing something?

    1. Correction the code to call it is

      Call ThisWorkbook.update_duties(1)

      I have also tried it with a test function in the ThisWorkbook model and get the same type mismatch error

      Function Test_Update()
      Dim i As Long
      i = 1
      update_duties (i)
      End Function

    2. You haven’t said which line has the error.

      One thing I noticed. In the code “For Each c In Range(daily_ranges(day – 1))” you need to say which workbook the Range is from or it will use the active worksheet.

      1. Thanks for the reply.
        The line causing the error is :-
        Call ThisWorkbook.update_duties(1)
        I thought passing the 1 would work as the day parameter is long.

        Regarding the named range (daily_ranges) i have it used elsewhere in a function on my ThisWorkbook code and it doesn’t error out with specifying a workbook.

  24. Hi, Paul

    If Sheet1.Range(“J10”).Value = “” Then Call MsgBox(“Silaka isi menu terlebih dahulu”, vbInformation, “Isi Menu”) ElseIf Sheet1.BAYAR.Value = “” Then Call MsgBox(“Silakan Melakukkan pembayaran terlebih dahulu”, vbInformation, “Cetak Struk”) ElseIf Sheet1.Range(“H10”).Value = “” Then Call MsgBox(“Silaka isi nama terlebih dahulu”, vbInformation, “Cari data”) ElseIf Sheet1.KEMBALIAN.Value < 0 Then Call MsgBox("Maaf Uang tidak cukup untuk melnjutkan transaksi", vbInformation, "Transaksi Gagal") ElseIf Sheet2.Range("F5:F500").Value < 0 Then Call MsgBox("Maaf Stok Barang Minus", vbInformation, "Stok Barang Minus") Else Sheet1.Range("J10:Q26").Copy Sheet4.Select HasilTransaksi.Offset(1, 3).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Call SimpanKode Sheet7.PrintOut protect ElseIf Sheet2.Range("F5:F500").Value

    ElseIf Sheet2.Range("F5:F500").Value < 0

    Then doesn't work The error appears as “run-time error 13 – Type mismatch”.

  25. I have a text file which I read and store in the variable name vdiFile(which contents computer name like BNL60001 basically computer name is alpha numeric).
    Now I want to check that an array which has computer names ( same as above) matches the vdifile content.
    Although both contains VDI names in same format as mentioned above it give me Type Mismatch Error.

  26. hi, Paul actually i have the problem about type mismatch 13 despite this code works for somebodies but for me it gives me this error this is strange the error in this line
    For X = 1 To UBound(vArr)

    code is :
    Sub InsertSpaceBetweenDigitAndLetter1()
    Dim X As Long, Z As Long, LastRow As Long, vArr As Variant
    Const DataColumn As String = “A”
    Const StartRow As Long = 1
    LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
    With Cells(StartRow, DataColumn).Resize(LastRow – StartRow + 1)
    vArr = .Value
    For X = 1 To UBound(vArr)
    For Z = Len(vArr(X, 1)) – 1 To 1 Step -1
    If Mid(vArr(X, 1), Z, 2) Like “#[A-Za-z]” Or Mid(vArr(X, 1), Z, 2) Like “[A-Za-z]#” Then
    vArr(X, 1) = Left(vArr(X, 1), Z) & ” ” & Mid(vArr(X, 1), Z + 1)
    End If
    .NumberFormat = “@”
    .Cells = vArr
    End With
    End Sub

    i hope getting explanation where is the problem

  27. Hi.

    I have mismatch 13 error.

    Dim datcelica As Range, zdatum As Long, datum As Date, pretekli As Date

    zdatum = Range(“A” & CStr(Rows.Count)).End(xlUp).Row

    For Each datcelica In Range(Cells(2, 1), Cells(zdatum, 1)).Cells
    datum = datcelica.Value
    pretekli = DateAdd(“d”, -60, Now)

    If datum < pretekli Then
    Range(Cells(datcelica.Row, 1), Cells(datcelica.Row, 4)).Delete Shift:=xlUp
    End If
    Columns(4).NumberFormat = "m/d/yyyy"

    The error appears in line: datum = datcelica.Value.

    Input date is for example 04.12.2020 14:30.

Leave a Reply

Your email address will not be published. Required fields are marked *