Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify offending cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify offending cell
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 --------------- Jacob Skaria "linglc" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify offending cell
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" wrote: 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 --------------- Jacob Skaria "linglc" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify offending cell
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 --------------- Jacob Skaria "linglc" wrote: 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" wrote: 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 --------------- Jacob Skaria "linglc" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify offending cell
Thanks! This helps.
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "linglc" wrote: 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" wrote: 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 --------------- Jacob Skaria "linglc" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Formula to Identify Cell | Excel Discussion (Misc queries) | |||
Identify the Current Cell | Excel Discussion (Misc queries) | |||
how to identify a cell address | Excel Programming | |||
Cell Ref to identify worksheet | Excel Programming | |||
pdf printing OFFENDING COMMAND: STACK: | Excel Programming |