PDA

View Full Version : Delete or ClearContents ends sub prematurely



speedracer
01-17-2007, 11:45 PM
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/tempdwnld/Server-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
01-18-2007, 01:57 AM
Hi i have commeted some lines below in your code


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
this is what i put togeher and tested perfectly!


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
Regards,
Simon

Bob Phillips
01-18-2007, 04:49 AM
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!


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


One small duplication



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

Simon Lloyd
01-18-2007, 07:08 AM
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 = Falseand of course back to true at the end but what do you think oh master!!

Regards,
Devoted Pupil

Bob Phillips
01-18-2007, 07:26 AM
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 :yes

speedracer
01-18-2007, 09:48 AM
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
01-18-2007, 09:51 AM
That's am 11.6Mb file!

I stopped the download as soon as I saw that.

speedracer
01-18-2007, 10:03 AM
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
01-18-2007, 10:25 AM
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
01-18-2007, 11:03 AM
Comboboxes?

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

lucas
01-18-2007, 11:14 AM
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
01-18-2007, 11:16 AM
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/tempdwnld/Server-Matrix-c.xls

Thanks again in advance.

SRM

lucas
01-18-2007, 11:21 AM
if you zip it you can attach it....

Bob Phillips
01-18-2007, 11:26 AM
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
01-18-2007, 11:30 AM
I never have a need for zip anymore I dont use it. Why dont you want to pull it of my server?

Simon Lloyd
01-18-2007, 12:30 PM
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
01-18-2007, 12:47 PM
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
01-18-2007, 01:31 PM
OK then. All kinds of things I didnt know. Here it is.

Thx

speedracer
01-18-2007, 01:50 PM
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
01-18-2007, 02:07 PM
Was that an an swer to my question? If so, it doesn't explain anything for me.

speedracer
01-18-2007, 02:13 PM
sorry xld...that was code that was gven to me in #3 that I dropped into module 5 in addition to Macro4. It does the same thing basically, and also fails. Just cut and paste it as Macro6. It doesnt make a difference though, they both fail when it comes time to clearcontents or paste. I have noticed that if I delete all of the comboboxes in the Floorplan sheet, it works just fine. So it bols down to a problem with the comboboxes that I cannot figure out.

mdmackillop
01-18-2007, 02:20 PM
Have you tried stepping through the code? It shows that all 20 combobox items are refreshing. I didn't check for looping.
I've not tried to comprehend your code, but I would look for a way to dispense with them. I'm sure you could bring up the info in a userform which gets the data only when required.

Bob Phillips
01-18-2007, 02:23 PM
But what is the problem, where do the comboboxes come into it? It inserts a new row in the DateRecord sheet just as it is told to.

speedracer
01-18-2007, 02:23 PM
sorry xld. That code was given to me in #3. It basically does the same thing as Macro4. Just paste it into that module. It hangs to just like the other one at the point at which it hits clearcontents or paste of anykind. The code thereafter is skipped and the sub ends.

I deleted the comboboxes in the florplan sheet and it worked fine. I cant figure out why this would be, because the data that populates the combobox is not even on the daterecord sheet. Very perplexing.

johnske
01-18-2007, 02:31 PM
Unable to duplicate stated problem...

speedracer
01-18-2007, 02:33 PM
What we have is a need to organize a schedule of food servers on a floorplan. We have various stations, some better than others. We need to make a way to create flooplans and keep track of how many times each server has had each type of station over the past 90 days. We want to display that in the combobox when you pull it down. I would also like to be able to see in the combobox when I pull it down, some kind of marker or color change to a name is it has already been selected. So when each combo box has a value selected, all of the other comboboxes refresh.

As for the date record sheet, I will need to do ther things after I shift the data. My concern is that nothing after the clearcontents or paste instruction executes. Therefore I cannot continue to build on that. I should be able to. I was not aware that when you clearcontents in an instruction that the entire sub terminates thereafter. I dont think that is supposed to be the case.

I need to know why the comboboxes have anything to do with the operation of a sub that deals with a sheet that doesnt even populate the comboboxes.

At the bottom of the daterecord sheet, I have countif totals for the number of times each server has eah station over the last 90 days. The ServerTallies sheet populates the comboboxes, and the ServerTallies sheet does read the totals at the bottom of the DateRecrod sheet.

This is the only way I can think to lay this out. It seemed like it was going to be a prety simple project. If anyone can think of antoher way to lay this out, then I would be happy to send you a pack of LifeSavers.

Thanks

SRM

speedracer
01-18-2007, 02:35 PM
But it doesnt execute any instructions thereafter. Thats not all I need to do. Its just the start of a sequence of instructions. Here...insert some kind of instruction after that clearcontent or paste code. You will see that it does not execute. Why?

speedracer
01-18-2007, 02:42 PM
Unable to duplicate stated problem...

What do you mean? You are able to get other instructions to execute after the clearcontents or past instruction? I agree that it appears that the code is running to completion, but it is not. You need to look closer.

Cut paste and Run this:

Sub Test2()

With Sheets("DateRecord")
.Range("B93:AZ93").ClearContents
.Range("B4:AZ92").Cut Destination:=.Range("B5")
End With
Worksheets("DateRecord").Cells(2,1)="WORKING"
Sheets("FloorPlan").Select

EndSub

Does it insert the text into cell B2? No. Does it go back to the floorplan sheet? No. It is skipping over these instructions and ending the sub. Like I said, if I delete the comboboxes, it works fine. Without the comboboxes, the entire thing is pointless.

johnske
01-18-2007, 03:04 PM
What do you mean? You are able to get other instructions to execute after the clearcontents or past instruction? I agree that it appears that the code is running to completion, but it is not. You need to look closer.

Cut paste and Run this:

Sub Test2()

With Sheets("DateRecord")
.Range("B93:AZ93").ClearContents
.Range("B4:AZ92").Cut Destination:=.Range("B5")
End With
Worksheets("DateRecord").Cells(2,1)="WORKING"
Sheets("FloorPlan").Select

EndSub

Does it insert the text into cell B2? No. Does it go back to the floorplan sheet? No. It is skipping over these instructions and ending the sub. Like I said, if I delete the comboboxes, it works fine. Without the comboboxes, the entire thing is pointless.Does it insert the text into cell B2? Yes. Does it go back to the floorplan sheet? Yes... = Unable to duplicate problem

johnske
01-18-2007, 03:26 PM
BTW that's not saying you don't have a problem, but it indicates the problem has nothing to do with that particular bit of code as that's the only bit I ran

speedracer
01-18-2007, 03:36 PM
Does it insert the text into cell B2? Yes. Does it go back to the floorplan sheet? Yes... = Unable to duplicate problem

You inserted this into my file? Or did you put it in a new workbook?

Please send me back the entire workbook in the form in which you got the desired result, with the new sub as well, and I will run it here. Perhaps it is some odd anomally in my computer. I can also run it on antoher machine if need be. I need to find the problem here.

Because it doesnt work here. Same file. Same code. Different result. Seems odd.

Thanks

SRM

johnske
01-18-2007, 03:52 PM
Inserted into your attached file in post #18... You can do the same.

Could be a machine problem or maybe a workbook corrupted by running some of the other code. I am personally unwilling to look any further because you have undeclared variables all over the place, but I would suggest you use Option Explicit and explicitly define all of your variables, then try to rationalize your code before proceeding further with your project. :)