Tracing the cell.Address or different cells in defined range which caused Type Mistmatch Error 13

[NimishK]

Hello


Now knowing the cause of "Type Mistmatch Error 13"
How can i know which cell or different cells which gave me Type Mismatch Error 13
column (Z) contained Formula value ie =X2*Y2 . By mistake the cell in Column(Y) was typed with something else rather implementing the formula
eg in cell (Y276) was typed with 25000/2 rather than =25000/2. and cell (X276) was completely blank. and cell(Z276) was displayed with #Value instead of O (zero). I had to search physically on the worksheet #Value which raised the error and then corrrected the cell Y276 with =25000/2
So wondering if code could be generated in WorkBook_Open Event with msgbox to trace the cell Address or different cells in defined range which caused error. Eg MSGBOX "Cell" Z276 contains "#Value"

Any thread reference also will do

NimishK

 

[DanteAmor]

Try this for Sheet1

Private Sub Workbook_Open()
    Dim sh As Worksheet, rng As Range, c As Range
    Set sh = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    On Error Resume Next
    Set rng = sh.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Cells
    On Error GoTo 0
    If rng Is Nothing Then
        MsgBox "There are no cells with error"
    Else
        For Each c In rng
            cad = cad & c.Address(0, 0) & vbCr
        Next
        MsgBox "Cells contains [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Value]#Value[/URL] " & vbCr & cad
    End If
End Sub

-----------
But you could also fix the formula with something like this:

=IFERROR(X2*Y2,"")

 

[NimishK]

Thanks DanteAmor

BTW what have you defined cad as. It msgd. Variable not defined. Should i define as DIM cad As Variant
and could you explain why you have used (xlCellTypeFormulas, 16) in below syntax

Set rng = sh.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Cells

Thanks

 

[DanteAmor]

Try :

Private Sub Workbook_Open()
    Dim sh As Worksheet, rng As Range, c As Range, [COLOR=#0000ff]cad as string[/COLOR]
    Set sh = Sheets("Sheet1")
    On Error Resume Next
    Set rng = sh.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Cells
    On Error GoTo 0
    If rng Is Nothing Then
        MsgBox "There are no cells with error"
    Else
        For Each c In rng
            cad = cad & c.Address(0, 0) & vbCr
        Next
        MsgBox "Cells contains [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Value]#Value[/URL]  " & vbCr & cad
    End If
End Sub

SpecialCells(xlCellTypeFormulas, 16 = with errors


https://docs.microsoft.com/en-us/office/vba/api/excel.xlspecialcellsvalue


XlSpecialCellsValue enumeration (Excel)

Specifies cells with a particular type of value to include in the result.


Name Value Description
xlErrors 16 Cells with errors.
xlLogical 4 Cells with logical values.
xlNumbers 1 Cells with numeric values.
xlTextValues 2 Cells with text.

 

[NimishK]

Thanks for the Correction from Variant to String .
Really Appreciate your guidance
Thank you so much
NimishK

 

[DanteAmor]

Thanks for the Correction from Variant to String .
Really Appreciate your guidance
Thank you so much
NimishK

Im glad to help you. Thanks for the feedback.