Type Mismatch Error

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am using the below code to highlight cells and add comments in the main sheet "Audit Plan". The macro is initiated by clicking a button in the "Control" Sheet and from there the code will sun the StatusUpdate code in the 'Audit Plan' sheet and then it will run the WeeklyTrackerNewAudits code in the Check_WeeklyTracker sheet.

Excel Formula: 
Option Explicit

Sub ReconCheck()

Call StatusUpdate
Call WeeklyTrackerNewAudits

  
MsgBox ("Updates highlighted and noted")


End Sub


Sub StatusUpdate()

Dim i As Long, lrow As Long, iCol As Long, dt As String
Dim sh As Worksheet
Set sh = Sheets("Audit_Plan")

iCol = sh.Range("A1").CurrentRegion.Columns.Count
lrow = sh.Range("J" & Rows.Count).End(xlUp).Row
dt = Format(sh.Range("K3"), "mm/dd/yyyy") & " - "
    
sh.Activate
For i = 7 To lrow
    If Cells(i, "G").Value <> Cells(i, "CR").Value Then
        Cells(i, 1).Resize(, iCol).Interior.ColorIndex = 6
            If Cells(i, "G").Value = "In Progress" And Cells(i, "CR").Value = "Not Started" Then
                Cells(i, 83).Value = "Other"
                Cells(i, 84).Value = dt & "Status Updated from 'Not Started' to 'In Progress' - Need to update in SharePoint"
            End If
    End If
    If Cells(i, "G").Value = "Cancelled" Then
        Cells(i, 83).Value = "Remove from Audit Plan"
        Cells(i, 84).Value = dt & "Cancelled per Weekly Tracker"
    End If
    If Cells(i, "G").Value = "Completed" And Cells(i, "CR").Value <> "Completed" Then
        Cells(i, 83).Value = "Published - Report Not Received"
        Cells(i, 84).Value = dt & "Published per Weekly Tracker"
    End If
Next i

  
End Sub

Sub WeeklyTrackerNewAudits()

Dim j As Long, lrow As Long, dt As String
Dim sh3 As Worksheet, sh2 As Worksheet
Set sh3 = Sheets("Check_WeeklyTracker")
Set sh2 = Sheets("Control")

lrow = sh3.Range("A" & Rows.Count).End(xlUp).Row
dt = sh2.Range("B10")
    
sh.Activate
For j = 6 To lrow
    If Cells(j, "K").Value > dt And IsError(Cells(i, "V").Value) Then
        Cells(j, 1).Interior.ColorIndex = 3
    End If
Next i
   
   
End Sub

I am getting a Run-time error '13': Type mismatch in the section below and I'm not sure what I did wrong. Separated each code runs perfectly, but combined in does not run as smooth.
This is where the error occurs.
VBA Code: 
For i = 7 To lrow
    If Cells(i, "G").Value <> Cells(i, "CR").Value Then

What am I missing when I combine into one module?


Thank you,
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
VBA Code: 
    If Cells(i, "G").Value <> Cells(i, "CR").Value Then

Is it the line above that produces the runtime error?

Or is the one of the line that follow?

VBA Code: 
    If Cells(i, "G").Value <> Cells(i, "CR").Value Then
        Cells(i, 1).Resize(, iCol).Interior.ColorIndex = 6
            If Cells(i, "G").Value = "In Progress" And Cells(i, "CR").Value = "Not Started" Then
                Cells(i, 83).Value = "Other"
                Cells(i, 84).Value = dt & "Status Updated from 'Not Started' to 'In Progress' - Need to update in SharePoint"
            End If
    End If
 
Upvote 0
rlv01 said:
VBA Code: 
    If Cells(i, "G").Value <> Cells(i, "CR").Value Then

Is it the line above that produces the runtime error?

Or is the one of the line that follow?

VBA Code: 
    If Cells(i, "G").Value <> Cells(i, "CR").Value Then
        Cells(i, 1).Resize(, iCol).Interior.ColorIndex = 6
            If Cells(i, "G").Value = "In Progress" And Cells(i, "CR").Value = "Not Started" Then
                Cells(i, 83).Value = "Other"
                Cells(i, 84).Value = dt & "Status Updated from 'Not Started' to 'In Progress' - Need to update in SharePoint"
            End If
    End If
Click to expand...
It is the first line of that section that is highlighted when the error pops up and a click on debug.

However, if you see any other mistakes I made or improvements to the code. Please feel free to let me know. I’m open to any corrections.
 
Upvote 0
Add some debug code to help you understand what is causing the runtime error:

VBA Code: 
Debug.Print "--------------"
Debug.Print "I = " & i & ", GValue: " & Cells(i, "G").Value & " : " & "GType: " & TypeName(Cells(i, "G").Value)
Debug.Print "I = " & i & ", CRValue: " & Cells(i, "CR").Value & " : " & "CRType: " & TypeName(Cells(i, "CR").Value)
If Cells(i, "G").Value <> Cells(i, "CR").Value Then
 
Upvote 0
Hello,

I found my mistake. I had an N/A error in one of my formulas within the column that was causing the mismatch. Removing it gave no issues at all.
Thank you for your suggestion.

Questions:
(1) Is the below code efficient?
(2) Would it be a good idea to add an error handling code with this code?

VBA Code: 
Option Explicit

Sub ReconCheck()

Call StatusUpdate
Call WeeklyTrackerNewAudits

  
MsgBox ("Updates highlighted and noted")


End Sub

Sub StatusUpdate()


Dim i As Long, lrow As Long, iCol As Long, dt As String
Dim sh As Worksheet
Set sh = Sheets("Audit_Plan")

iCol = sh.Range("A1").CurrentRegion.Columns.Count
lrow = sh.Range("J" & Rows.Count).End(xlUp).Row
dt = Format(sh.Range("K3"), "mm/dd/yyyy") & " - "
    

sh.Activate
For i = 7 To lrow
    If Cells(i, "G").Value <> Cells(i, "CR").Value Then
        Cells(i, 1).Resize(, iCol).Interior.ColorIndex = 6
            If Cells(i, "G").Value = "In Progress" And Cells(i, "CR").Value = "Not Started" Then
                Cells(i, 83).Value = "Other"
                Cells(i, 84).Value = dt & "Status Updated from 'Not Started' to 'In Progress' - Need to update in SharePoint"
            End If
    End If
    If Cells(i, "G").Value = "Cancelled" Then
        Cells(i, 83).Value = "Remove from Audit Plan"
        Cells(i, 84).Value = dt & "Cancelled per Weekly Tracker"
    End If
    If Cells(i, "G").Value = "Completed" And Cells(i, "CR").Value <> "Completed" Then
        Cells(i, 83).Value = "Published - Report Not Received"
        Cells(i, 84).Value = dt & "Published per Weekly Tracker"
    End If
Next i


End Sub
Sub WeeklyTrackerNewAudits()

Dim i As Long, lrow As Long, dt As String
Dim sh3 As Worksheet, sh2 As Worksheet
Set sh3 = Sheets("Check_WeeklyTracker")
Set sh2 = Sheets("Control")

lrow = sh3.Range("A" & Rows.Count).End(xlUp).Row
dt = sh2.Range("B10")
    
sh3.Activate
For i = 6 To lrow
    If Cells(i, "K").Value > dt And IsError(Cells(i, "V").Value) Then
        Cells(i, 1).Interior.ColorIndex = 3
    End If
Next i
   
   
End Sub
 
Upvote 0
1. "Efficient" really only matters if the execution is too slow for you. If you are happy with the execution time, then it is efficient enough.
2. If your formulas are going to throw a lot of #N/A errors then perhaps. The question is how you want to handle it. If you have a specific plan in mind for what should happen when a formula returns #N/A , then adding error handling will help. If not, then having the code throw a runtime error is not an unreasonable way to know something is wrong.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
User-defined type not defined - vba/excel error
Replies
4
Views
221
RoryA
RoryA
N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
115
MARK858
MARK858
D
  • Question Question
Copy cell $G$1 (value only) 1 cell to the right of the checkbox when clicked
Replies
1
Views
171
Artik
Artik
C
  • Question Question
Need to add to my search worksheet
Replies
1
Views
104
craigwojo
C
Noid67
  • Question Question
Multiple worksheet change and Formula use
2
Replies
17
Views
713
mumps
mumps

Forum statistics

Threads
1,223,997
Messages
6,175,872
Members
452,679
Latest member
darryl47nopra

Share this page

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
Back
Top