0

I have an output from an old system that lists hundreds of pages. These are grouped by dates. Since it's an old system, it dumps the output and I import into excel all in column A.

I want to count how many instances of something is between each date range.

So my source is: Note the date Tue Sep 10 08:52:40 2019 is date report run and not needed.

Call Date: 08/01/2019

6:47 0:01:04 35905 34312
7:19 0:04:50 33365 34312
7:16 0:12:58 36050 34312
7:45 0:16:06 33206 34312
8:52 0:17:48 33649 34312
9:35 0:02:38 33160 34312
9:29 0:10:10 32735 34312
9:43 0:00:12 34311 34312
9:40 0:04:16 33008 34312
10:19 0:09:18 33805 34312
10:25 0:06:00 32735 34312

Tue Sep 10 08:52:40 2019 Location: Page 2

Call Date: 08/01/2019 (cont.)

7:19 0:04:50 33365 34312
7:16 0:12:58 36050 34312
7:45 0:16:06 33206 34312
8:52 0:17:48 33649 34312
9:35 0:02:38 33160 34312
9:29 0:10:10 32735 34312
9:43 0:00:12 34311 34312
9:40 0:04:16 33008 34312
10:19 0:09:18 33805 34312
10:25 0:06:00 32735 34312

Tue Sep 10 08:52:40 2019 Location: Page 3

Call Date: 08/03/2019

7:21 0:02:40 37332 34312
7:46 0:00:38 32075 34312
7:49 0:08:02 37606 34312
8:52 0:02:24 33420 34312
8:58 0:01:40 33519 34312
8:59 0:02:20 36039 34312
9:13 0:00:42 35956 34312
9:09 0:04:58 32891 34312
9:19 0:00:18 35338 34312
9:24 0:01:44 33546 34312

Tue Sep 10 08:52:40 2019 Location: Page 4

I think the logic is set two variables startRow and endRow with startRow = "Call Date:" and endRow = "Location:"

Then using a foreach next loop, loop through each range between the startRow and endRow and countif contains ":"

The problem I have is how do I first find the start of the range and end of a range, but that range can reoccur many times. So was thinking a For Each Next loop??

````vba
  Set lastRow = .Cells.Find("Location:", .Cells(1, 1), xlValues, xlPart, , xlPrevious)
  Set firstRow = .Cells.Find("Call Date:", .Cells(1, 1), xlValues, xlPart, , xlPrevious)

For Each dateRange In ActiveSheet.Range("firstRow:lastRow")  //not sure how to set and loop through each occurrence of first & last row
COUNTIF(firstRow:lastRow,":") //count the rows containing ":"
Next dateRange

I would like the output to be (in column C):

Call Date: 08/01/2019 19 calls //remember this date and others can span multiple pages

Call Date: 08/02/2019 10 calls

Call Date: 08/03/2019 6 calls

etc...

Thank you very much!

Mr80s
  • 65
  • 1
  • 8
  • Better logic would be as follows: Do a foreach loop for first to last cell, and start a counter. Then Do a `Search` in every cell to determine if "Call date" is in it. Then set a counter to the row. Then for each hit you get, substract the old counter from the new, and that's your number of rows. Write that in column C. I'll post an answer with functional code in a bit. – Plutian Oct 30 '19 at 12:44
  • Think `Find` is the way to go. Set range variables to both Find operations and then your range of interest is the bit between the two. – SJR Oct 30 '19 at 13:23

1 Answers1

0

As I stated, a for each row counting all rows between your rows of interest would be my way to go:

Sub counter()
Dim cel As Range
Dim i As Integer
Dim lastr As Integer
Dim calldate As String

i = 0
lastr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row 'determine last row of data

For Each cel In Sheet1.Range("A1:A" & lastr) 'start loop

If InStr(cel.Value, "Call Date") Then 'check if your value is "Call date" indicating start of data
    If calldate = "" Then 'check if this is the first loop
        calldate = cel.Value 'set calldate to the current loop.
            Else 'if not first loop, write the current calldate + counter to the next available blank cell
            Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = calldate & " " & i
            i = 0 'reste counter
            calldate = cel.Value 'save next calldate value
    End If
    Else
        If cel <> "" Then 'test if cell is blank, skip if it is
            If InStr(cel, "Location") Then 'test if cell holds "Location, indicating it is not data. Skip if it is
                Else
                i = i + 1 'increase counter if part of data
            End If
        End If
End If
Next cel
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = calldate & " " & I & " calls" 'Write current data at end of loop.
End Sub
Plutian
  • 2,276
  • 3
  • 14
  • 23
  • Wow! I'm very impressed Plutian. Where did you learn this? I've got a lot of research to do but thank you very much! – Mr80s Oct 30 '19 at 14:38
  • Thank you, happy to help. I've learned this by trail and error mostly, and studying answers from users on here much smarter and more experienced than me. – Plutian Oct 30 '19 at 14:51
  • I wonder, if using this logic it's possible to find all calls on a date that are before or after a time. So count all calls before 07:00:00 and after 23:00:00. Basically, any calls from 11:01pm to 6:59am – Mr80s Oct 30 '19 at 15:29
  • @Mr80s yes this is certainly possible, however as this is an additional question unrelated to your original issue, it's best to post a new question with your new requirements. ([I got shouted at](https://stackoverflow.com/questions/58610083/need-help-to-perform-cut-paste-with-vba-from-one-workbook-to-another/58610697?noredirect=1#comment103540433_58610697)) – Plutian Oct 30 '19 at 15:33
  • Thank you and good point. I created a new post https://stackoverflow.com/q/58629083/11989241 – Mr80s Oct 30 '19 at 15:49
  • @Mr80s it's best to add your working code as well. You can copy paste from my answer if you want, but if you don't add any code your post will likely be ignored or downvoted. – Plutian Oct 30 '19 at 15:55