-
Aug 12th, 2022, 09:57 AM
#1
[RESOLVED] Counting and finding years
I have a worksheet which contains 4 rows of data for each day of several months. Dates like 8/1/2022 are displayed in d-mmm-yy format like 1-Aug-22. I need to 1) determine how many months of data there are and 2) determine the address of the last row of the first month (or the address of the first row of the second month) so that I can cut the first month and paste it to a different sheet.
For 1) I know I can step through each row looking for a Day() value of 1 and then dividing by 4 (since there are 4 days 1s for each month) but I was hoping for something more elegant like a Worksheetfunction.countif with a wildcard search for the day 1s.
-
Aug 12th, 2022, 05:29 PM
#2
Frenzied Member
Re: Counting and finding years
Originally Posted by MartinLiss
I have a worksheet which contains 4 rows of data for each day of several months. Dates like 8/1/2022 are displayed in d-mmm-yy format like 1-Aug-22. I need to 1) determine how many months of data there are and 2) determine the address of the last row of the first month (or the address of the first row of the second month) so that I can cut the first month and paste it to a different sheet.
For 1) I know I can step through each row looking for a Day() value of 1 and then dividing by 4 (since there are 4 days 1s for each month) but I was hoping for something more elegant like a Worksheetfunction.countif with a wildcard search for the day 1s.
(1) why are you only sending the first month to another different sheet? - why are you not sending all of the months to other sheets?
you could simply run down the sheet outputting each line to the appropriate other sheet until there if nothing to report i.e. cell being referenced = ""
just thinking out loud
-
Aug 12th, 2022, 07:18 PM
#3
Re: Counting and finding years
Originally Posted by incidentals
(1) why are you only sending the first month to another different sheet?
It is a part of an archiving process. Over time, without this, the main worksheets could have a lot of months added to it and the archiving process limits the number of months on the main sheet by cutting and pasting one month to the archive sheet when a new month is added to the main sheet.
Last edited by MartinLiss; Aug 12th, 2022 at 07:36 PM.
-
Aug 13th, 2022, 04:43 AM
#4
Re: Counting and finding years
depending if you want a formula or vba
assuming all the dates are in a single column like column A
=INT((MAX(A:A)-MIN(A:A))/30)
of course just calculating for 30 day months only is a bit rough, if you can use vba then datediff would be more accurate
to get the row number of the last entry for the first month, by formula, no looping required
assumes the data (date) in column A starts on row 2, change to suit
=MATCH(DATE(YEAR(A2),MONTH(A2)+1,1)-1,A:A,1)
if you want to automate moving the rows then vba could be a better option
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 13th, 2022, 06:45 AM
#5
Frenzied Member
Re: Counting and finding years
Originally Posted by MartinLiss
It is a part of an archiving process. Over time, without this, the main worksheets could have a lot of months added to it and the archiving process limits the number of months on the main sheet by cutting and pasting one month to the archive sheet when a new month is added to the main sheet.
how many months does the main sheet hold - for sanity purposes?
you could trigger the archival process when you enter the month xmonths since first month mentioned...
The process would end up archiving the first month on you add the xth+1 and there after.
you only need compare actions in a date cell where -mmm- is first row date cell -mmm- and they are xmonths apart thus triggering the archival process.
that's a trigger of cells in range when table changes.
the last row is unimportant as the action happens as soon as you enter the first occurance of the xth month.
just thinking out loud
-
Aug 13th, 2022, 08:23 AM
#6
Re: Counting and finding years
Thanks for your suggestions but I've decided to keep doing it inelegantly like this which both gives me the count of months and the ending address of the first month.
Code:
For lngRow = 3 To lngLastRow
If Month(.Cells(lngRow, "A")) <> intOldMonth And .Cells(lngRow, "A").Text <> "" Then
intMonthCount = intMonthCount + 1
If intMonthCount = 2 Then
strEndAddrMo1 = .Cells(lngRow, "A").Offset(-1, 0).Address
End If
intOldMonth = Month(.Cells(lngRow, "A"))
End If
Next
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|