PDA

View Full Version : Solved: Unexpected error.



mdmackillop
04-23-2010, 08:52 AM
This code is showing errors for which I see no reason. Can anyone replicate this behaviour? (Excel 2007)

If I comment out the "rCell.value = 1" line, only the first line shows an error.

:doh:



Option Explicit
Sub Test()
Dim shtB As Range
Dim rCell As Range

Set shtB = Sheet1.Cells(1, 1).Resize(5)
shtB.ClearContents
For Each rCell In shtB
rCell.Value = 1
If Err.Number > 0 Then
rCell.Value = Err.Number & " - " & Err.Description
Err.Clear
End If
Next
End Sub

tkaplan
04-23-2010, 09:29 AM
I don't get any errors when I run the code...

Paul_Hossler
04-23-2010, 10:51 AM
This code is showing errors for which I see no reason.


Compile time of Run Time errors?

Either way, I don't get any.

Are you trying to trigger Err object?


If Err.Number > 0 Then
rCell.Value = Err.Number & " - " & Err.Description
Err.Clear
End If


Paul

mdmackillop
04-23-2010, 10:59 AM
Just tried it again in a new workbook with this result

GTO
04-23-2010, 11:03 AM
No error in 2000. Maybe a blonde moment, but as there is no resume next showing, should not it bug out? A total stab, but iit would take you just a moment. Add an err.Clear above the Set statement.

mdmackillop
04-23-2010, 11:13 AM
The puzzle is that there should be no error. I cam across this in another thread and simplified the code as far as possible to determine the cause. I just don't see why Error 9 should occur.

GTO
04-23-2010, 11:22 AM
Hi Malcom,

You are no doubt more knowledgeable, I am just thinking of the simple stuff. Did you add the err.Clear? Does the code stop when it errors?

tpoynton
04-23-2010, 11:59 AM
I dont see the error in 2007. When I tried to create an error with your code (rCell.value = 1/0), the debugger kicks in and the error is displayed on screen, not on the worksheet like yours are. When I added 'on error resume next', I did get error 11 printed on the sheet...but no error 9 using your code. Weird.

mdmackillop
04-23-2010, 12:05 PM
This is the thread (http://www.vbaexpress.com/forum/showthread.php?t=31692) where I noticed the problem. mbarron's code worked, but my unexpected error overwrites the good results.

Paul_Hossler
04-23-2010, 03:53 PM
I didn't have the 'interesting' experience with my copy of 2007

Added 2 small things that 'should' not make a difference, but might be worth trying


Option Explicit
Sub Test()
Dim shtB As Range
Dim rCell As Range

Set shtB = Sheet1.Cells(1, 1).Resize(5,1) ' added ,1
shtB.ClearContents
For Each rCell In shtB.Cells ' added .Cells
rCell.Value = 1
If Err.Number > 0 Then
rCell.Value = Err.Number & " - " & Err.Description
Err.Clear
End If
Next
End Sub


Paul

mdmackillop
04-23-2010, 04:46 PM
Hi Paul,
Still the same error, and if I Rem out rCell.Value = 1 , then only the first line shows an error.
Most peculiar.

Paul_Hossler
04-23-2010, 05:03 PM
1. Have you re-started your computer recently?

2. What happens if you use Worksheets ("Sheet1"). instead of Sheet1. ?

Paul

mdmackillop
04-24-2010, 01:34 AM
New day, fresh start, same problem!

Case 0, Col A = "OK"
Case 1, Col A = ""
Case 2, Col A = Nothing

mdmackillop
04-24-2010, 01:34 AM
and the file

Aussiebear
04-24-2010, 02:13 AM
Tested in 2007 and I don't get any errors at all

mdmackillop
04-24-2010, 02:28 AM
Thanks Ted. I'll try an update/repair and see if it goes away.

mdmackillop
04-24-2010, 05:22 AM
Repair didn't work so I've reinstalled. Errors have gone away.
Thanks for the feedback and suggestions.