VBA - If range value = "date" then

[bobunknown]

Airtight this is my best crack at it.

Private Sub Workbook_Open()
Dim intMonth As Integer, intDay As Integer, intYear As Integer
Dim strDate As String
intMonth = Month(3)
Sheets("LongStayPermit").Select
    Columns("M:M").Select
    ActiveSheet.Range("$M$1:$M$244").AutoFilter Field:=1, Criteria1:= _
    lngDataLen = Range("M" & Rows.Count).End(xlUp).Row
    Cells.Select
For intMonth = 1 To 12
    strDate = Format(DateSerial(intYear, intMonth + 1, 0), "dd/mm/yyyy")
    Debug.Print strDate
    
        If lngDataLen > 1 Then
        Selection.Copy
        Sheets(MonthName(intMonth)).Range("A1").PasteSpecial
    End If
Next
    
Sheets("LongStayPermits").Select
    ActiveSheet.Range("$M$1:$M$244").AutoFilter Field:=1
End Sub

My thoughts on this is to remove the need for using the date to filter and use just the month. hence the change to IntMonth. Ill explain why.
(the rest of the code may be garbage but its what looked somewhat right to me though I know there is an issue with the line starting lngDataLen, looking in to that now :banghead

The data i'm looking at relates to parking permits. There are fields such as:
Date Issued, Name, address, Registration and Expiry Date (contained in column M:M).

All I am doing is filtering the data in to months (that they expire) and placing that information in the corresponding, named, sheet.

The sheet I have in front of me has 13 tabs, one with the main load of data labelled "LongStayPermits" and the rest are labelled as their corresponding month Starting from the end of March (Financial Year).

Hopefully this explains it a little better.

 

[Gasman]

Try this in a debug window

? month(3)

the value is 1. You need Month = 3, but that is being overwritten with the loop, so no good either.


Can you add a helper column?
This is a column to 'help' you. If so and it can be as far right as you want so as not to see it all the time), but take it into account if you do not want to copy it.


My thoughts are to put a formula of =Month(Expirydate) in that column, then just filter on month names in that column ?

 

[bobunknown]

Just so you know how my brain is working atm ill explain my thought process on the code I just posted.

lines 2,3 & 4 to my understanding are defining the D/M/Y as integers and setting the initial month as 3.

4,5,6,7, & 8 are the pre proven lines for selecting the initial worksheet, filtering and then selecting the cells.

Beyond that my understanding is limited as I was simply working with the code you sent me. But at a guess it is code to cycle each sheet by month and copy data in to it.

Shoot me if im wrong (Probably)

But now you know where I am coming from more or less.

 

[Gasman]

Just so you know how my brain is working atm ill explain my thought process on the code I just posted.

lines 2,3 & 4 to my understanding are defining the D/M/Y as integers and setting the initial month as 3.

Not quite. Month takes a date value, which is really just a number, so 3 is the 3rd day since 01/01/1900, hence the month will be January and number 1.
If we wanted June we would actually set intMonth to 6 or use Month(with a date in June)

4,5,6,7, & 8 are the pre proven lines for selecting the initial worksheet, filtering and then selecting the cells.

Pretty much. Does not help not having any line numbers.

What we want to do *I think* is

Identify our data for each copy
Select it all and paste to the correct sheet
Continue until we have copied all the data.

Can we have that helper column, as it would make life so much easier
Also if we know that we will *always* have data for each month, there is no need for lngDatalen

 

[bobunknown]

Hi again, I went home for the night.

So that we are both on the same page I have also attached a copy of the file I am working with so you can see for yourself and stop hearing my bad explanations.
I have removed all the personal info for obvious reasons but you should be able to see more clearly now what it is I am trying to do.


While I was at home I was also thinking that the code we are using to filter may be wrong as in the previous, long and clunky code, I used I simply pulled that code from the recorder each time. Not sure but just an idea.

Edit:
After comparing the codes I see what you were trying to do, ignore the above paragraph, switch just clicked in me head

 

[Gasman]

Hi again, I went home for the night.

Good idea.

So that we are both on the same page I have also attached a copy of the file I am working with so you can see for yourself and stop hearing my bad explanations.
I have removed all the personal info for obvious reasons but you should be able to see more clearly now what it is I am trying to do.

That helps a lot

While I was at home I was also thinking that the code we are using to filter may be wrong as in the previous, long and clunky code, I used I simply pulled that code from the recorder each time. Not sure but just an idea.

Edit:
After comparing the codes I see what you were trying to do, ignore the above paragraph, switch just clicked in me head

OK, if we can insert a colum in front of A, would that cause you any problems?


I got a subscript out of range when I opened the file.
As I mentioned before I don't think you want this on an Open event?, just have a macro and run as required.?


What is meant to happen to VOIDS, if anything?

 

[Gasman]

They are not even dates?, just text that 'looks' like a date?

 

[Gasman]

Hi again, I went home for the night.

Good idea, worth taking a break and regroup

So that we are both on the same page I have also attached a copy of the file I am working with so you can see for yourself and stop hearing my bad explanations.
I have removed all the personal info for obvious reasons but you should be able to see more clearly now what it is I am trying to do.

That helps a lot

While I was at home I was also thinking that the code we are using to filter may be wrong as in the previous, long and clunky code, I used I simply pulled that code from the recorder each time. Not sure but just an idea.

Edit:
After comparing the codes I see what you were trying to do, ignore the above paragraph, switch just clicked in me head

Having seen that the 'dates's are not really dates, just text that looks like a date, my idea of the extra column is not going to work without extra work determing real dates.?


Also check cell M87
What happens to VOIDS if anaything?

 

[Gasman]

Ok,
As you haven't come back so far, I've gone with my original plan.
It will need tidying up, but the functionality is there, in that it copies the data to the sheets.


See sub TestCode.


HTH

 

[Gasman]

Here is another version without the helper columns.
There is a problem at present as for month 1, the date is 31st December of the previous year?, but it should give you something to work with.

 

[bobunknown]

Hey dude, I wasn't snubbing you I work in different departments depending on the day and need etc. not good for continuation of my projects but what can you do

I'm giving what you put on a look over now but thanks in advance

 

[bobunknown]

Now I see what you have done it makes sense.

I'm goanna need to study it and Google some (most) of what you have done to build my understanding. I think at the moment it my lack of understanding of VBA terminology that is holding me back, but that should come with time.

Thanks again.

 

[Gasman]

Now I see what you have done it makes sense.

I'm goanna need to study it and Google some (most) of what you have done to build my understanding. I think at the moment it my lack of understanding of VBA terminology that is holding me back, but that should come with time.

Thanks again.

You are welcome Bob.

 

[bobunknown]

Right I've been though all the code you posted, line by line, googled what I didn't understand and its been a big help. Learned a lot about loops, which is bound to come in handy and VBA in general. Think I just need to get in to the VBA mind-set as its like a whole new language.

To get the code working for my purpose I have altered just one line (if your still interested)

strDate = Format(DateSerial(intYear + 1, intMonth + 1, 0), "dd.mm.yyyy")

I added the plus one after intYear so that I get the expiry dates that I want. I only really need the ones in the next year as a new spread sheet will be made each new year and the old one archived, so after this year there wont be any expiry dates in there for the current year only the following.

Further to my understanding of how it is written, as there are only 12 months as defined by the line intMonth the loop will never cycle enough times to move another year ahead an thus create an error.

Please correct me if I am wrong as I an deffo goanna start moving on to other projects now.

Thanks for all the help

 

[bobunknown]

Spoke to soon, put the code in to a copy of the live sheet bound it to a button and now I get an out of Range error :banghead:

Gonna try solve this one myself, see if I haven't learned something.

Is it always this easy

 

[Gasman]

That looks good, and avoids the problem I mentioned.
I did not know what your setup was, so could only give you something to work from.


Be careful of data entry errors like that one I mentioned on row 87 That will not be moved anywhere.
Also I am puzzled as to why you are not using dates, but values that 'look' like dates.?,


Anyway, good luck with it.

 

[Gasman]

Spoke to soon, put the code in to a copy of the live sheet bound it to a button and now I get an out of Range error :banghead:

Gonna try solve this one myself, see if I haven't learned something.

Is it always this easy

Always


Use the debugger and walk though it with F8.
If you hover over each variable, it will show you it's value and you can see if it is what you expect.

If you get stuck, upload it and I'll take a look again.

 

[bobunknown]

I'm working from a existing table of data, (not even my own this is all for a co-worker) and it looks like it was never properly formatted.

As for this error I'm thinking that it is definitely related to the line

Sheets(MonthName(intMonth)).Range("A1").PasteSpecial

For some reason when there is more data in the table it wont paste data past January despite the format being exactly the same as my test bed and it working every time I test there flawlessly. :banghead:

 

[Gasman]

I suspected as much, there must be a sheet named for every month name.

 

[bobunknown]

Use the debugger and walk though it with F8.
If you hover over each variable, it will show you it's value and you can see if it is what you expect.

That's a neat trick I see clearly now

its expecting the sheet name to correspond with the intMonth in this case intMonth = 2 yet they are all named not numbered.