Type 13 Mismatch Error from Reference Error

[Whylucky]

Hi All,

I have the below code which looks to see if a certain cell contains a phrase, either bearing or figure. However since this cell is a linked value there is a chance that an error (#Ref!) will show instead. I thought my code would search for the error and if it did not exist would move on, but instead it gives the Type Mismatch 13 Error. I have tried rearranging the code but the error still comes up. Does anyone have any suggestions on making this work? Any help would be greatly appreciated.

If Range("R" + CStr(g)).Value = CVErr(xlErrRef) Then        
Broken = True
    Else
        If InStr(Range("BC" + CStr(g)).Value, "Figure") Then
            Figure = True
            Broken = False
        ElseIf InStr(Range("BC" + CStr(g)).Value, "Bearing") Then
            Figure = False
            Broken = False
            If Range("R" + CStr(g)).Value = CVErr(xlErrRef) Then
                Broken = True
            End If
        End If
    End If

Regards,
Whylucky

 

[par60056]

a little trick, use '&' for string concatenation rather than '+' and it will automatically convert the numbers.

Use the iserror function to determine if a cell contains an error.

try this:

If IsError(Range("R" & g).Value) Then
    Broken = True
Else
    If InStr(Range("BC" & g).Value, "Figure") Then
        Figure = True
        Broken = False
    ElseIf InStr(Range("BC" & g).Value, "Bearing") Then
        Figure = False
        Broken = False
        If IsError(Range("R" & g).Value) Then
            Broken = True
        End If
    End If
End If

 

[Whylucky]

That worked great, thank you, the extra advice about the "&" was also very helpful