Writing value to cell triggers "Application-Defined Error" 1

[VBAjedi]

The following code module has an odd problem (sorry, line wrapping is lousy): the last line attempts to write a value (in this case, a date) to a cell, which generates an "Application-defined or object-defined error". I can read the value from the cell, and I can verify the value I want to write to the cell. The code executes properly up to the Stop command. The problem line works correctly if I move it to another module. I have tried writing a different variable value, writing a constant (i.e. "1&quot, writing to a different cell, all to no avail.
Some other line in the code must be causing this - any ideas what is wrong? support.Microsoft.com doesn't seem to have the answer either.

Thanks!
VBAjedi

Sub RotateMonths()
'
' RotateMonths Macro
' Macro recorded 12/18/02 by Obie PC Support
'

Dim ConfirmAction As Integer
Dim FirstPeriodDate As Date
Dim x

Sheets("AvailabilityTracker&quot.Activate

ConfirmAction = MsgBox("Warning: This will clear the projected sales " _
& "data for " & Range("S3&quot.Value & ". This action cannot " _
& "be undone. Proceed?", vbOKCancel)
If ConfirmAction = 2 Then End ' user clicked "Cancel" button.
Range("A6&quot.AutoFilter ' Turns off AutoFilter and displays all
FirstPeriodDate = Range("S3&quot.Value
Range("W6R10000&quot.Select
Selection.Copy
'Range("SQ&quot.MergeCells = False
Range("S6N10000&quot.Select ' DK or DN?
ActiveSheet.Paste

Range("DR6R10000&quot.Clear
Range("S3&quot.Select
Range("A6&quot.AutoFilter Field:=1 ' Turns AutoFilter back on
FirstPeriodDate = (FirstPeriodDate + 28)
Stop
Sheets("AvailabilityTracker&quot.Range("S3&quot.Value = FirstPeriodDate

End Sub

 

[RobBroekhuis]

You get an error even writing a constant to a different, unrelated cell? I'm wondering if the problem is somehow related to the autofilter (without knowing how it's configured, it's hard to tell). Does the error occur if you don't turn it on?
Rob

 

[VBAjedi]

Good call, Rob, but problem not solved. Commenting out the "Autofilter on" line stops the error. So I tried moving the "Autofilter on" line after the "Write value" line and putting a stop in between them. The code now executes up to the "Write Value" line, then terminates WITHOUT an error message. It does not update the cell value or get to the Stop that I placed before the "Autofilter on" line! Apparently the very presence of that "Autofilter on" line in the code is enough to cause the "Write value" error (even before the Autofilter line executes).

 

[RobBroekhuis]

Very strange. Is error handling turned off? Can you post your modified code? If you put a stop just before the write value, and manually (using the immediate window) try to set the cell value, what happens?
Rob

 

[VBAjedi]

Rob,

I have no special error handling routines in place. Using the immediate window to manually change the value of the cell in break mode has the same result: immediate termination of the macro without an error message and without changing the value of the cell. The autofilter statement is definitely the problem, but why? All I'm doing is turning the autofilter on by selecting the first data cell in a range and filtering on that column with no criteria. It's always worked before, and the same "autofilter on" code works in other modules within this project! Here's what I changed in the code (it runs to the first stop, then terminates on resume):

Range("S3&quot.Select
FirstPeriodDate = (FirstPeriodDate + 28)
Stop
Sheets("AvailabilityTracker&quot.Range("A3&quot.Value = FirstPeriodDate
Stop
Range("A6&quot.AutoFilter Field:=1 ' Turns AutoFilter back on

End Sub

 

[VBAjedi]

Rob,

I still don't understand the problem, but I have come up with a workaround. Moving the offending "Autofilter on" statement to another Sub and calling that Sub from my current Sub seems to work fine. Wierd, huh?

Thanks for your help troubleshooting!

VBAjedi

 

[RobBroekhuis]

I wish we'd found a REAL solution. I'm really puzzled by this. If you'd like to see if it's something about your Excel version, feel free to mail me a version of your workbook that has the problem behavior, and I'll see what it does on my side. broekhrr@apci.com
Rob

 

[rmikesmith]

VBAjedi,

Just to weigh in here: While I obviously don't have your data setup, I tried to simulate some of your code/worksheet. With AutoFilter turned on the code was able to write to a cell with no problem. This was true whether the written-to cell was above or below the auto-filtered data. It seems to me this problem is the result of something specific with your workbook that is not readily apparent rather than an issue with AutoFilter, per se. Having someone else look at the workbook, as Rob suggested, is probably the best next step. I, too, would be willing to take a look if this is OK with you. My email is rmikesmith@earthlink.net

Regards,
Mike

 

[VBAjedi]

Aaagh! Problem still not solved.
Rob, I've emailed you my spreadsheet to look at.
Mike, if Rob can't figure it out, I'll be happy to give you a crack at it too!

Thanks for your help on this perplexing problem!

VBAjedi

 

[RobBroekhuis]

I do not know EXACTLY how the application error comes about, but I do know how to solve the error. In two subs, one of which is your worksheet_change event handler, you use an
if .. then end
statement. End ends the execution of the macros, period. What you need instead is
if .. then exit sub

After making this change in two places (also in the RecalculateRow sub), I think things are working properly.
Give it a try on your end.
Rob

 

[VBAjedi]

Hmmm. . . seems my last reply did not post. . . It was something like this:

Thanks again, Rob, for the excellent analysis! I was more careful in testing before declaring the problem fixed, but it looks like it really is this time.

VBAjedi