Run-time error '13': Type mismatch on large font line

Nlhicks

Nlhicks

Board Regular
Joined
Jan 8, 2021
Messages
244
Office Version
  1. 365
Platform
  1. Windows
Sub DoXfmrMath1()

Dim wsUpdate As Worksheet
Dim i As Long
Dim wb As Workbook
Dim wbFacility As Workbook
'Dim wsUpdate As Worksheet

Const cstrPath As String = "C:\Users\nhicks\Documents\Ratings\Saved Versions\"
Const cstrWbFacility As String = "WAPA-UGPR Facility Rating and SOL Record (Master).xlsm"
Const cstrwsUpdate As String = "Xfmr Update"

For Each wb In Workbooks
If LCase(wb.Name) = LCase(cstrWbFacility) Then
Set wbFacility = wb
Exit For
End If
Next wb
If wbFacility Is Nothing Then
If Dir(cstrWbFacility) <> "" Then
Set wbFacility = Workbooks.Open(cstrWbFacility)
Else
MsgBox "Could not find '" & cstrWbFacility & "' in current folder. Please open workbook and start again.", vbInformation, "Ending here"
GoTo end_here
End If
End If
If Evaluate("ISREF('[" & cstrWbFacility & "]" & cstrwsUpdate & "'!A1)") Then
Set wsUpdate = wbFacility.Sheets(cstrwsUpdate)
Else
MsgBox "Sheet '" & cstrUpdate & "' not found in workbook '" & cstrWbFacility, vbInformation, "Ending here"
GoTo end_here
End If
With wsUpdate
For i = 0 To 1
If .Cells(8 + (i * 4), "D").Value <> .Cells(8 + (i * 4), "E").Value Then
.Cells(11 + i, "P").Value = .Cells(8 + (i * 4), "E") - .Cells(8 + (i * 4), "D")
End If

If .Cells(10 + (i * 4), "D").Value <> .Cells(10 + (i * 4), "E").Value Then
.Cells(11 + i, "Q").Value = .Cells(10 + (i * 4), "E") - .Cells(10 + (i * 4), "D")
End If

If .Cells(16 + (i * 4), "D").Value <> .Cells(16 + (i * 4), "E").Value Then
.Cells(11 + i, "S").Value = .Cells(16 + (i * 4), "E") - .Cells(16 + (i * 4), "D")

End If
If .Cells(18 + (i * 4), "D").Value <> .Cells(18 + (i * 4), "E").Value Then
.Cells(11 + i, "T").Value = .Cells(18 + (i * 4), "E") - .Cells(18 + (i * 4), "D")
End If
Next i
' End If
End With
Call Xfmr_Bold_in_Concatenate1

end_here:
Set wsUpdate = Nothing

End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Nlhicks said:
Run-time error '13': Type mismatch on large font line
.Cells(11 + i, "P").Value = .Cells(8 + (i * 4), "E") - .Cells(8 + (i * 4), "D")
Click to expand...

It is possible that in some of the cells of column D or E you have letters or blank spaces.

Try the following to identify which cell has the problem so you can review it. I added an error handler and a msgbox.
VBA Code: 
Sub DoXfmrMath1()
  Dim wsUpdate As Worksheet
  Dim i As Long
  Dim wb As Workbook
  Dim wbFacility As Workbook
  Dim cstrUpdate
  'Dim wsUpdate As Worksheet
 
  Const cstrPath As String = "C:\Users\nhicks\Documents\Ratings\Saved Versions\"
  Const cstrWbFacility As String = "WAPA-UGPR Facility Rating and SOL Record (Master).xlsm"
  Const cstrwsUpdate As String = "Xfmr Update"
 
  For Each wb In Workbooks
    If LCase(wb.Name) = LCase(cstrWbFacility) Then
      Set wbFacility = wb
      Exit For
    End If
  Next wb
 
  If wbFacility Is Nothing Then
    If Dir(cstrWbFacility) <> "" Then
      Set wbFacility = Workbooks.Open(cstrWbFacility)
    Else
      MsgBox "Could not find '" & cstrWbFacility & "' in current folder. Please open workbook and start again.", vbInformation, "Ending here"
      GoTo end_here
    End If
  End If
 
  If Evaluate("ISREF('[" & cstrWbFacility & "]" & cstrwsUpdate & "'!A1)") Then
    Set wsUpdate = wbFacility.Sheets(cstrwsUpdate)
  Else
    MsgBox "Sheet '" & cstrUpdate & "' not found in workbook '" & cstrWbFacility, vbInformation, "Ending here"
    GoTo end_here
  End If
 
  With wsUpdate
    For i = 0 To 1
      If .Cells(8 + (i * 4), "D").Value <> .Cells(8 + (i * 4), "E").Value Then
        On Error GoTo displaymsg
        .Cells(11 + i, "P").Value = .Cells(8 + (i * 4), "E") - .Cells(8 + (i * 4), "D")
        On Error GoTo 0
      End If
     
      If .Cells(10 + (i * 4), "D").Value <> .Cells(10 + (i * 4), "E").Value Then
        .Cells(11 + i, "Q").Value = .Cells(10 + (i * 4), "E") - .Cells(10 + (i * 4), "D")
      End If
     
      If .Cells(16 + (i * 4), "D").Value <> .Cells(16 + (i * 4), "E").Value Then
        .Cells(11 + i, "S").Value = .Cells(16 + (i * 4), "E") - .Cells(16 + (i * 4), "D")
      End If
     
      If .Cells(18 + (i * 4), "D").Value <> .Cells(18 + (i * 4), "E").Value Then
        .Cells(11 + i, "T").Value = .Cells(18 + (i * 4), "E") - .Cells(18 + (i * 4), "D")
      End If
    Next i
    ' End If
  End With
 
  Call Xfmr_Bold_in_Concatenate1
 
end_here:
  Set wsUpdate = Nothing
   
displaymsg:
MsgBox "Check cells E" & 8 + (i * 4) & " and D" & 8 + (i * 4)

End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.
 
Upvote 0
Yes okay that is it, some of the spaces have 120/140 so then it needs to be what type? I tried Variant and it did not work so I made it a string and then I get the same error but on the i
 
Upvote 0
It's not a problem with the macro, it's a problem with your data. You must correct your data.
I don't understand what you mean by this:
Nlhicks said:
some of the spaces have 120/140
Click to expand...

If you have spaces or letters, then try the following:

Rich (BB code): 
      If .Cells(8 + (i * 4), "D").Value <> .Cells(8 + (i * 4), "E").Value Then
        .Cells(11 + i, "P").Value = Val(.Cells(8 + (i * 4), "E")) - Val(.Cells(8 + (i * 4), "D"))
      End If
 
Upvote 0
Solution
Thank you, I am not sure that is going to work because if the block says 130/120 and someone wants to change that to 120/100 then I need the math to take 130-120 and 120-100 and provide the difference I suppose in the form of -10/-20 MVA. I am working with my lead on how to solve this but I think what you suggested above will likely come into play. He mentioned that the Pri/Sec should only take on the value of the Pri so maybe I can go in and override those to all be number that certainly would simplify my life.
 
Upvote 0
Apparently I selected the one row that has the limit written like that but, I am glad that I did. Otherwise when I go and try to show people how to use it I probably would have found it then and had the issue of trying to figure it out.
 
Upvote 0
Can you help me write this code so if this one and only time issue occurs then the code knows how to handle it. I think it would be something like:
If IsError Then GoTo DoubleRating

DoubleRating:
If .Cells(8 + (i * 4), "D").Value <> .Cells(8 + (i * 4), "E").Value Then
.Cells(11 + i, "P").Value = Val(.Cells(8 + (i * 4), "E")) - Val(.Cells(8 + (i * 4), "D"))
End If
But will that automatically look at both values and do that math?
 
Upvote 0
So I rewrote it like this: The large print row is giving the error Invalid or Unqualified reference.

Dim i As Integer
Dim wb As Workbook
Dim wbFacility As Workbook
Dim Update As Worksheet


Const cstrWbFacility As String = "WAPA-UGPR Facility Rating and SOL Record (Master).xlsm"
Const cstrUpdate As String = "Xfmr Update"

For Each wb In Workbooks
If LCase(wb.Name) = LCase(cstrWbFacility) Then
Set wbFacility = wb
Exit For
End If
Next wb
If wbFacility Is Nothing Then
If Dir(cstrWbFacility) <> "" Then
Set wbFacility = Workbooks.Open(cstrWbFacility)
Else
MsgBox "Could not find '" & cstrWbFacility & "' in current folder. Please open workbook and start again.", vbInformation, "Ending here"
GoTo end_here
End If
End If
If Evaluate("ISREF('[" & cstrWbFacility & "]" & cstrUpdate & "'!A1)") Then
Set Update = wbFacility.Sheets(cstrUpdate)
Else
MsgBox "Sheet '" & cstrUpdate & "' not found in workbook '" & cstrWbFacility, vbInformation, "Ending here"
GoTo end_here
End If
With Update
For i = 0 To 1
If .Cells(8 + (i * 4), "D").Value <> .Cells(8 + (i * 4), "E").Value Then
.Cells(11 + i, "P").Value = .Cells(8 + (i * 4), "E") - .Cells(8 + (i * 4), "D")
On Error GoTo DoubleRating
End If

If .Cells(10 + (i * 4), "D").Value <> .Cells(10 + (i * 4), "E").Value Then
.Cells(11 + i, "Q").Value = .Cells(10 + (i * 4), "E") - .Cells(10 + (i * 4), "D")
End If

If .Cells(16 + (i * 4), "D").Value <> .Cells(16 + (i * 4), "E").Value Then
.Cells(11 + i, "S").Value = .Cells(16 + (i * 4), "E") - .Cells(16 + (i * 4), "D")
End If
If .Cells(18 + (i * 4), "D").Value <> .Cells(18 + (i * 4), "E").Value Then
.Cells(11 + i, "T").Value = .Cells(18 + (i * 4), "E") - .Cells(18 + (i * 4), "D")
End If
Next i
' End If
End With

Call Xfmr_Bold_in_Concatenate1

end_here:
Set wsUpdate = Nothing
Set wbFacility = Nothing

DoubleRating:

If .Cells(8 + (i * 4), "D").Value <> .Cells(8 + (i * 4), "E").Value Then
.Cells(11 + i, "P").Value = Val(.Cells(8 + (i * 4), "E")) - Val(.Cells(8 + (i * 4), "D"))
End If
If .Cells(10 + (i * 4), "D").Value <> .Cells(10 + (i * 4), "E").Value Then
.Cells(11 + i, "Q").Value = Val(.Cells(10 + (i * 4), "E")) - Val(.Cells(10 + (i * 4), "D"))
End If
If .Cells(16 + (i * 4), "D").Value <> .Cells(16 + (i * 4), "E").Value Then
.Cells(11 + i, "S").Value = Val(.Cells(16 + (i * 4), "E")) - Val(.Cells(16 + (i * 4), "D"))
End If
If .Cells(18 + (i * 4), "D").Value <> .Cells(18 + (i * 4), "E").Value Then
.Cells(11 + i, "T").Value = Val(.Cells(18 + (i * 4), "E")) - Val(.Cells(18 + (i * 4), "D"))
End If
Return

End Sub
 
Upvote 0
I used your code and it told me to check cells E8 and D8
 
Upvote 0
The 120/100 is a primary/secondary rating. So an adjustment could come in as 120/95 or 115/95 or 125/100
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Runtime Error 13: Type Mismatch
Replies
2
Views
195
NateSC
NateSC
G
  • Solved
Stop excel macro from running if there is a blank cell in a column
Replies
5
Views
163
RobP
R
U
  • Question
Type Mismatch Error
Replies
9
Views
206
Alex Blakenburg
A
S
  • Question
Doubleclick on cell in Protected Sheet
Replies
4
Views
320
shoheiyuna
S
J
  • Question
Combining VBA code together...need help!
Replies
3
Views
178
Fluff
Fluff

Forum statistics

Threads
1,215,212
Messages
6,123,654
Members
449,113
Latest member
Hochanz

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