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

[Nlhicks]

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

 

[Nlhicks]

Here is where the math gets done:

 

[Nlhicks]

If an exception can be written for this one case then the other cases work just fine.

 

[DanteAmor]

I still don't understand what you want to do in this case:

What result do you expect?

-----
Or in this case, what result do you expect?

-----

If in those cases you are not going to perform the subtraction, after declaring the variables then add this instruction in your macro:

On Error Resume Next

In future please use code tags when posting code How to Post Your VBA Code it makes your code easier to read & copy, thereby increasing you chances of getting help.

 

[Nlhicks]

Well, What I would want is for the math to take 150-200 and give me -50 and then take 75-100 and give me -25 but write it like -50/-25 and paste it into the right box.

I am thinking about writing on error go to DoubleRating: then writing MsgBox "Please do these calculations by hand, they differ from the rest of the ratings and this code will not calculate them for you, sorry" but that is kind of taking the easy way out and I don't really want to do that.

 

[Nlhicks]

Here is a snapshot of the spreadsheet and you can see the one line hat differs from all of the rest. I will also run into an issue when I pull up lines that have --- in them but I am pretty sure I know how to deal with those since I had them on my last sheet. The 200/100 is a whole new issue that I didn't realize that I had until now.

 

[DanteAmor]

I can't help you that way.

• You are not putting the information coherently.
• At the beginning of your post you have an image, now you put another image.
• In post #13, I give the example 200/100 and you respond with another example: 150-200. So I still don't understand what to do.
• Your images are not complete, the rows and columns are not visible.
• Use the XL2BB tool to put examples from your sheet.

 

[Nlhicks]

I cannot use XL2BB from my work computer and the information on the first image is being pulled from the second image. I simply provided the images to help you understand how the code is working. The only issue if figuring out how to do the math when the the spreadsheet in image two has 200/100 and the user wants to update that limit to 150/75 the code needs to do the math for the user such as (150-200/75-100) giving the result in the Delta Value of -50/-25 MVA

 

[Nlhicks]

My work will no allow us to use any add ins

 

[Nlhicks]

Never mind, even when I try to make the math work in a basic excel worksheet, excel gets confused and instead of keeping the answers in its fractional form it solves it into a a numerical value. I will have to come up with a different solution.