Delete or ClearContents ends sub prematurely

[speedracer]

I have tried this several ways with the same failed result. I finally tried simply recording a macro while performing the procedure manually. Nothing stopped me from performing it manually.

I then simply tired to run the macro of the procedure I just performed, and it doesn't work. It ends right after the clearcontents instruction. The contents are cleared, but the macro ends without running the instructions thereafter. This happens if I use the delete command as well. Trying to clear the contents of cells or a range in this workbook seems to present problems no matter how I do it.

Why is this?

Here is the code. I am simply trying to shift all of the data in a range down one row so I can then insert new data, and keep a constant record over the last 90 days. So this is supposed to erase the data in the last row (90 days ago) and copy the data above (the previous 89 days) and paste it down one row.

This seems so simple, so I am very frustrated.

The entire workbook is too big to attach but can be downloaded here:

http://www.moments-photo.biz/tempdwn...ver-Matrix.xls


Sub Macro4()
'
' Macro4 Macro
' Macro recorded 1/18/2007
'
'
Sheets("DateRecord").Select
Range("B4").Select
Range("B93:AZ93").Select
Selection.ClearContents
Range("B4:AZ92").Select
Selection.Cut
Range("B5").Select
ActiveSheet.Paste
Sheets("FloorPlan").Select

End Sub

[Simon Lloyd]

Hi i have commeted some lines below in your code

[vba]
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 1/18/2007
'
'
Sheets("DateRecord").Select ''''''you don't need to select a sheet in order to manipulate it
Range("B4").Select''''''you could loose this line as it appears to do nothing
Range("B93:AZ93").Select
Selection.ClearContents''''this works and clears the contents of row 93
Range("B4:AZ92").Select'''''new selection
Selection.Cut
Range("B5").Select'''''this is where you want to paste the info - Works fine for me
ActiveSheet.Paste
Sheets("FloorPlan").Select

End Sub
[/vba]this is what i put togeher and tested perfectly!

[vba]
Sub Test()
'
With Sheets("DateRecord")
.Range("B93:AZ93").ClearContents
.Range("B4:AZ92").Cut Destination:=Sheets("DateRecord").Range("B5")
End With
Sheets("FloorPlan").Select
End Sub
[/vba]Regards,
Simon

[Bob Phillips]

Hi i have commeted some lines below in your code

Does this mean your code is very fast, with a long tail?

this is what i put togeher and tested perfectly!

[vba]
Sub Test()
'
With Sheets("DateRecord")
.Range("B93:AZ93").ClearContents
.Range("B4:AZ92").Cut Destination:=Sheets("DateRecord").Range("B5")
End With
Sheets("FloorPlan").Select
End Sub
[/vba]

One small duplication

[vba]

Sub Test()
'
With Sheets("DateRecord")
.Range("B93:AZ93").ClearContents
.Range("B4:AZ92").Cut Destination:= .Range("B5")
End With
Sheets("FloorPlan").Select
End Sub
[/vba]

[Simon Lloyd]

Hi i have commeted some lines below in your code

Does this mean your code is very fast, with a long tail?

Yes of course Bob, you can see it from where you are on the south westerly horizon - weather permitting!

One small duplication

VBA:

Sub Test() ' With Sheets("DateRecord")
.Range("B93:AZ93").ClearContents
.Range("B4:AZ92").Cut Destination:= .Range("B5")
End With Sheets("FloorPlan").Select
End Sub

I originally had this Bob but when i ran the code it copied to the sheet that was currently visible......but having seen yours i know what i did wrong...i had this

Range("B5")

instead of

.Range("B5")

But as you can see i do listen to you, no selecting where not needed short and sweet code, in fact the only thing i think i missed was

Application.ScreenUpdating = False

and of course back to true at the end but what do you think oh master!!

Regards,
Devoted Pupil

[Bob Phillips]

Yes of course Bob, you can see it from where you are on the south westerly horizon - weather permitting!

I will keep a look-out for it

I originally had this Bob but when i ran the code it copied to the sheet that was currently visible......but having seen yours i know what i did wrong...

In many ways you were lucky the testing found the error. If the sheet had been active, you code would work (Then), but might fail later.

But as you can see i do listen to you, no selecting where not needed short and sweet code,

I noticed that, I smiled and gave quiet applause. I am sure your skills are being driven by your determination to assist in other forums.

I am pleased with my pupil

[speedracer]

OK...I ran my code from before again just to see if I was crazy, and then I ran the different variations you guys gave me. All of them worked in a brand new blank test sheet i started. However, when I dropped them into my workbook they fail.

Can someone please try using on or all of these in my workbook, because they will not work, and I just cannot tell why.

I provided a link for the workbook above.

Running the following code that was proved above worked just great on a new blank sheet with dummy data in it. However, when I put it in my workbook, it wont run at all. The sub just ends. No error message. Nothing.

Sub Test2()

With Sheets("DateRecord")
.Range("B93:AZ93").ClearContents
.Range("B4:AZ92").Cut Destination:=.Range("B5")
End With
Sheets("FloorPlan").Select

End Sub


It keeps ending on the clearcontents part. That seems to be the hang up. Why would it do that?
Thanks

SRM

[Bob Phillips]

That's am 11.6Mb file!

I stopped the download as soon as I saw that.

[speedracer]

I dropped in a couple of instructions after the with statement just as a test. I numbere the lines here for reference sake.

Sub Test2()

1 With Sheets("DateRecord")
2 .Range("B93:AZ93").ClearContents
3 .Range("B4:AZ92").Cut Destination:=.Range("B5")
4 End With
5 Sheets("FloorPlan").Select

End Sub

Nothing after line 3 executes. I looked like it was line 3, but I noticed the that data does get pasted in the right place. Why does it just stop after that, when there are other instructions? I have started deleting everything out of that page except the relevant data range, with no change in results.

I do have other cells that are reading the data from that range with countif formulas that provide counts that are referenced by the set of cells that populate the comboboxes (all below row 94) . Maybe thats it. I deleted that too and it didnt help. I dont know. Im stumped.

SRM

[speedracer]

OK...something in the comboboxes is the problem. cant see it though because the comboboxes do not get any data from the DateRecord Sheet. However, there are totals at the bottom of the DateRecord sheet that the ServerTallies sheet reads and uses to populate the comboboxes.

I need to make those comboboxes quasi-dynamic. I want them to display totals next to each name. Those totals do not change each time you change the value of the combobox though. They only change once you hit the finalize button. Then all of the data is stored in the ranges that populate the comboboxes.

I am really lost now. I dont see why that wont work.

[Bob Phillips]

Comboboxes?

Can you create a workbbok that shows the problem, but is a sensible size?

[lucas]

Speedracer has started a new thread which addresses the same question. For background on this thread:
http://vbaexpress.com/forum/showthread.php?t=11023

[speedracer]

yes, here it is. If I take the backround image out of the file, it gets much smaller of course. It is still a little bit too large to attach (290kb) so here is a URL again:

http://www.moments-photo.biz/tempdwn...r-Matrix-c.xls

Thanks again in advance.

SRM

[lucas]

if you zip it you can attach it....

[Bob Phillips]

The code that you show in #8 is not in the workbook, it is your original code.

How do we re-create the problem?

[speedracer]

I never have a need for zip anymore I dont use it. Why dont you want to pull it of my server?

[Simon Lloyd]

Speedracer - For courtesy sake please download WinZip evaluation version for free - compress your workbook - attach it and the responses will come thick and fast.

We have tried to help you, please help us to help you further!

Regards,
Simon

[lucas]

if you have xp it's built in....in windows explorer just right click on the file and select "send to" compressed zipped folder...............

[speedracer]

OK then. All kinds of things I didnt know. Here it is.

Thx

[speedracer]

yes this is the same problem that was started before. It started as a different question, but has ended up being the same problem. Thanks though...

[Bob Phillips]

Was that an an swer to my question? If so, it doesn't explain anything for me.