Results 1 to 6 of 6

Thread: [RESOLVED] Counting and finding years

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Resolved [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.

  2. #2
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Counting and finding years

    Quote Originally Posted by MartinLiss View Post
    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

  3. #3

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: Counting and finding years

    Quote Originally Posted by incidentals View Post
    (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.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  5. #5
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Counting and finding years

    Quote Originally Posted by MartinLiss View Post
    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

  6. #6

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    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
  •  



Click Here to Expand Forum to Full Width