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
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.