Identify offending cell

[linglc]

I have these codes below. What I want to do is to identify in a message box
which cell has the error. So if I have text in cell A3, I want the message
box to tell me the error is in A3. Any ideas?

Sub ErrorExample()

On Error Resume Next

Dim total As Integer
Dim intA As Integer
Dim intB As Integer
Dim intC As Integer

intA = Range("A1")
intB = Range("A2")
intC = Range("A3")

total = intA + intB + intC

MsgBox ("Total is " & total)

End Sub

 

[Jacob Skaria]

Since the variables are declared as Integer it expects a integer value. If
one of the cell contains a text value it would give a typemismatch. I have
done a small change to your code as below which will retrieve 0 for a text
value...

Sub ErrorExample1()

On Error Resume Next

Dim total As Integer
Dim intA As Integer
Dim intB As Integer
Dim intC As Integer

intA = Val(Range("A1"))
intB = Val(Range("A2"))
intC = Val(Range("A3"))

total = intA + intB + intC

MsgBox ("Total is " & total)

End Sub

Also try the below example which will give you the sum and at the same time
tell you which cell is having a non-numeric value. Hope this helps

Sub ErrorExample2()

Dim rngTemp As Range
Set rngTemp = Range("A1:A3")
For Each cell In rngTemp
If Not IsNumeric(cell.Value) Then MsgBox _
cell.Address & " is having a non-numeric value"
Next
MsgBox "Sum of " & rngTemp.Address & " is " & _
WorksheetFunction.Sum(rngTemp)

End Sub

If this post helps click Yes

 

[linglc]

Thanks Jacob. The codes you have below would be able to point out which cell
has the error. However I am providing a simple example to illustrate my
point. In my problem, the codes are a lot longer and complex so the codes you
have may not work. Is there a way to to show what the offending cell is when
I get a runtime error message?

 

[Jacob Skaria]

You can number the lines as below and on error you can display the line
number with the error details.

Sub ErrorExample()

On Error GoTo ErrHandler

Dim total As Integer
Dim intA As Integer
Dim intB As Integer
Dim intC As Integer

1 intA = Range("A1")
2 intB = Range("A2")
3 intC = Range("A3")

4 total = intA + intB + intC

5 MsgBox ("Total is " & total)

Exit Sub
ErrHandler:
MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _
"Error: " & Err.Number & " " & Err.Description, vbCritical
End Sub


If this post helps click Yes

 

[linglc]

Thanks! This helps.

You can number the lines as below and on error you can display the line
number with the error details.

Sub ErrorExample()

On Error GoTo ErrHandler

Dim total As Integer
Dim intA As Integer
Dim intB As Integer
Dim intC As Integer

1 intA = Range("A1")
2 intB = Range("A2")
3 intC = Range("A3")

4 total = intA + intB + intC

5 MsgBox ("Total is " & total)

Exit Sub
ErrHandler:
MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _
"Error: " & Err.Number & " " & Err.Description, vbCritical
End Sub


If this post helps click Yes