+ Reply to Thread
Results 1 to 2 of 2

Locating which rows the first and last occurances of an entry fall on

  1. #1
    Registered User
    Join Date
    11-23-2007
    Posts
    65

    Locating which rows the first and last occurances of an entry fall on

    Hi guys,

    I hope this is a basic one!

    In column A of my spreadsheet I have the month of the year as a numeric value (i.e. 1 through to 12). The entries relate to the month that a certain activity (given in column B) was carried out. The entries are all in sequence (i.e. 1,1,1,1,2,2,2,2,2,2,3,3,3,4,4,4,4,4,4,4,4,5,5,5,5,......12,12,12). What I want to be able to do is find the row with the first instance of say month "1". I then want to find the row with the last occurance of the month "1". I will then use these rows as bounds for some other calculation work that I've got in my code. I'll apply the same logic to the other months of the year.

    It seems like this should be simple but I haven't written any code for a while and I can't work out how to do it at the moment.

    Any help would be much appreciated!

    Cheers,

    Chris

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Locating which rows the first and last occurances of an entry fall on

    Given your months are in sequence (ascending) you can use MATCH

    =MATCH(1,A:A,0) --> first row in which 1 appears
    =MATCH(1,A:A,1) --> last row in which 1 appears

    Remember of course you can use SUMIF, COUNTIF to sum/count data based on a criteria value.

    In VBA terms (misread)

    Please Login or Register  to view this content.
    By using Application.Match (as opposed to WorksheetFunction.Match) and storing result as Variant you can test the result to be numeric before doing any subsequent calcs (thereby avoiding debug) ... obviously if vStart is an Error then vEnd should be disregarded.
    Last edited by DonkeyOte; 05-21-2009 at 11:57 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1