Error handling when string variable returns text value RRS feed

  • Question

  • I am using a variable to capture a percentage but sometimes there is no percentage and so the cell contains the value "No Data" as text. When I run my code I get a Run-time error '13' type mismatch, what can I do to avoid this error?

    This is the part of the code that I think is relevant where I would have to add some sort of error handling:

    Dim myVal As String Dim NPSRng As Range Dim NPSString As String myVal = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value) Set NPSRng = Worksheets("Metrics").Range("A:A").Find(What:=myVal, LookAt:=xlWhole) NPS = NPSRng.Offset(0, 6).Value NPSString = FormatPercent(NPS, 2)

    This is the whole code:
    Sub Pipeline_EmailHLONetRegs()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Signature As String
    Dim mysht As Worksheet
    Dim myDropDown As Shape
    Dim myVal As String
    Dim RegRng As Range
    Dim PrevRegRng As Range
    Dim ManagerRng As Range
    Dim sourceRng As Range
    Dim RngAddress As String
    Dim LastRw As String
    Dim NPSRng As Range
    Dim QualityRng As Range
    Dim RefSourceString As String
    Dim FeeString As String
    Dim QualityString As String
    Dim NPSString As String
    Dim FeeRng As Range
    Set mysht = ThisWorkbook.Worksheets("Pipeline")
    Set myDropDown = mysht.Shapes("Drop Down 261")
    myVal = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)
    If myVal = "Choose HLO" Then
            MsgBox "Please Choose an HLO, then try again.", vbExclamation
            Exit Sub
        End If
    Set RegRng = Worksheets("Validation").Range("D:D").Find(What:=myVal, LookAt:=xlWhole)
    Set PrevRegRng = Worksheets("Validation").Range("D:D").Find(What:=myVal, LookAt:=xlWhole)
    Set ManagerRng = Worksheets("Validation").Range("D:D").Find(What:=myVal, LookAt:=xlWhole)
    Set sourceRng = Worksheets("Source").Range("A:A").Find(What:=myVal, LookAt:=xlWhole)
    Set NPSRng = Worksheets("Metrics").Range("A:A").Find(What:=myVal, LookAt:=xlWhole)
    Set QualityRng = Worksheets("Metrics").Range("A:A").Find(What:=myVal, LookAt:=xlWhole)
    Set FeeRng = Worksheets("Metrics").Range("A:A").Find(What:=myVal, LookAt:=xlWhole)
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
    End If
    ActiveSheet.Range("$a$6:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
    ActiveSheet.Range("$A$7:$AV$1000").AutoFilter Field:=32, _
               Criteria1:=Array("="), Operator:=xlFilterValues, Criteria2:=Array(1, Now)
    ActiveSheet.Range("$A$7:$AV$1000").AutoFilter Field:=7, Criteria1:="<>DECL", _
            Operator:=xlAnd, Criteria2:="<>WITH"
    ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=1, Criteria1:=myVal
    NumberofRegs = RegRng.Offset(0, 1).Value
    PrevNumberofRegs = PrevRegRng.Offset(0, 2).Value
    Manager = ManagerRng.Offset(0, 3).Value
    NPS = NPSRng.Offset(0, 6).Value
    Quality = QualityRng.Offset(0, 4).Value
    Fee = FeeRng.Offset(0, 2).Value
    RefSource = sourceRng.Offset(0, 7).Value
    FormattedRefSource = Format(RefSource, "General Number")
    FeeString = FormatPercent(Fee, 2)
    QualityString = FormatPercent(Quality, 2)
    NPSString = FormatPercent(NPS, 2)
    RefSourceString = FormatPercent(RefSource, 2)
    Set rng = Nothing
    ' Only send the visible cells in the selection.
    'Set rng = ActiveSheet.Range("a6", ActiveSheet.Range("H6").End(xlDown))
    LastRw = ActiveSheet.Range("H6").End(xlDown).Row
      Set rng = Application.Union(ActiveSheet.Range("B6:H" & LastRw), _
                                  ActiveSheet.Range("N6:N" & LastRw), _
                                  ActiveSheet.Range("K6:K" & LastRw), _
                                  ActiveSheet.Range("P6:P" & LastRw))
    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected. " & _
               vbNewLine & "Please correct and try again.", vbOKOnly
        Exit Sub
    End If
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
    End With
    Signature = OutMail.HTMLBody
    strbody = "<br />" & "<br />" & ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1") & "<br />" & "Total Net Reg Pipeline " & Split(ActiveSheet.Range("B2").Text, ".")(0) & "<br />" & "Projection for this Month " & Split(ActiveSheet.Range("B5").Text, ".")(0) & "<br />" & "YTD Bank Referred = " & RefSourceString & "<br />" & "Fee Collection = " & FeeString & "<br />" & "QTD Quality Score = " & QualityString & "<br />" & "QTD NPS Score = " & NPSString & "<br />" & "Previous Month Registrations = " & PrevNumberofRegs & "<br />" & "Current Registrations MTD = " & NumberofRegs
    With OutMail
        .to = myVal
        .cc = Manager
        .Subject = "Production Snapshot"
        .HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & "</p>" & strbody & RangetoHTML(rng) & Signature
    End With
    On Error GoTo 0
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    ActiveSheet.Protect AllowFiltering:=True
    End Sub


    Monday, August 15, 2016 7:36 PM


  • Hi MEC,

    >> I am using a variable to capture a percentage but sometimes there is no percentage and so the cell contains the value "No Data" as text.

    You could add judgment code to check whether the value is number.

    Here is a simple code:

    Dim v As Variant
    v = ActiveCell.Value
    If IsNumeric(v) Then
    Debug.Print FormatPercent(v, 2)
    End If

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 16, 2016 2:24 AM