Define object error mid loop.

Hey Guys,
I just can't seem to see where I am going wrong with this code. What it does is uses filters out to a set criteria, I then make a new sheet named each day of the month "j"(Hence to 1 to 31 in the for statement) and copy the data to the new sheet. And this is where I get stuck, I then want to test if there is any data in "A2" in the new sheet and if there is not, delete that sheet. I am declairing the variable for "Val" but it keeps telling me that I am not. I just can't see what I am doing wrong.

any help would be very appreciated.

The problem area in the code:

Please Login or Register  to view this content.

The full code:

Please Login or Register  to view this content.

The following code

Please Login or Register  to view this content.

is stating that if there is a value, delete the sheet. Is this what you are aiming for? Also, you cannot set a string value, your line should be

Please Login or Register  to view this content.

I think it should look something like:

Please Login or Register  to view this content.

Let me know if this is what you are trying to accomplish.

I am trying to dissect your code and am wondering about the following line

Please Login or Register  to view this content.

Does rngD hold an address value in order to work properly with

Please Login or Register  to view this content.

? Also, if the sheet is in fact deleted, do you still want the ActiveSheet (which should now be Sheet 1 again)?

Browser error. Posted twice. Apologies.

Thanks Guy,

OK, @ Mordred,
Lord knows I tried to Dim every variable know to me to, but most times it would point me to the Val in the If statement and I know why now. You wrote Val.Value. I did not realize that you needed to do that. I thought b/c I had declared the variable Val with the vale that I did not need the .Value.
You were right about this code to:

Please Login or Register  to view this content.

It is not working now(Had been though). I am trying to use the filter criteria which value is in "B2".
So I am unable to test your code yet.

@rscsmith,
You are certainly right about the active sheet part. I want to name the worksheet variable after "j", but I kept getting an "Out of range" error, so I just stuck in the active sheet until I worked out the code for the if statement. How would I go about naming the new sheet variable "j"?
This is what I had tried before, I had this after the "If" statement
code:

Please Login or Register  to view this content.

Hi JapanDave

If I understand you correctly...

If you want to name the sheets 1, 2, 3, 4, etc, you would use:

Please Login or Register  to view this content.

Conversely, if you want to refer to a sheet named "1" or "2" then you would use:

Please Login or Register  to view this content.

I hope I've understood you correctly, in that you're using the sheet's name, not its Index.

Best regards, Rob.

Hi JapanDave, I used Val.Value because I set it as a Range, not a String, therefore you use the range's value to do your check.
@rscsmith

Please Login or Register  to view this content.

is not correct unless the A2's value is a cell address and Val is declared as a range.

Hi JapanDave, maybe it would be best if you uploaded a mock workbook with mock data in it so that we can try and walk through the code and the processes that you are trying to accomplish.

Hey Rob, Yes you would be correct. I am trying to refer to the sheet name.
Just a question, I am naming the sheet in this code:

Please Login or Register  to view this content.

So why can't I just refer to the sheet like this? BTW, this was giving me the out of range error.
Code:

Please Login or Register  to view this content.

And could you tell me the meaning of the "0" in the below code?
Code:

Please Login or Register  to view this content.

Naming the sheet j will name it say....4 (the iteration of the jth index) will name the sheet 4 while using j in an index of the sheets like

Please Login or Register  to view this content.

uses the 4th index, which will more than likely differ from the sheet name. Your new sheet that you added and named 4 might be the 6th sheet in the book.

Mordred,
The code is in Module 5

Dave

Thanks for the explanation, that makes sense now.

Your workbook kept crashing on me but give the following a try:

Please Login or Register  to view this content.

Let me know how it goes.

This suffices:

Please Login or Register  to view this content.

provided:
in cell C1: date0
Cell C2:C3 empty
in cell C4: date0

in cell D1: Date
in cell D4: Date

or (same functionality, more robust, different syntax)

Please Login or Register  to view this content.

Reasonably frustrated right now, I still can't get this code to do what I want.
I have uploaded the work book again, this time without the macro, so hopefuly it won7t crash.

Dave, didn't they tell you in your first Excel VBA course you never should use reserved names ?
And didn't they tell you not to be too shy to use the F2 button in the VBEditor or the F1 button to check ?
So using 'Val' for a variable name is not so sound an idea.

@, Incredible. That did the trick. You would not want to put in some comments to show exactly what you are doing? This workbook was an excercise for me to do something I have not been able to do.


@Snb, I am self taught, I guess it shows. But, you have no idea how much this helps me, so I really appreciate the help.