|re: If Cells(timeStamp, 1) = "TEXT" Then ignore|
Nice try, but your suggested solution of looking for text won't work because every cell in your "conversation" contains text.
The only reason we can do a time calculation is because we are extracting a text string (e.g. "6:44:26") and using the TimeValue function to turn it into a time. Look up the TimeValue function in VBA Help to see what I mean.
That said, I think I fixed your issues…
Multiple Blank Lines
We know that a conversation starts after the word Conversation and that it ends just before the first blank line.
Once we have good startChat and endChat values and have calculated an elapsed time for a single conversation, we don’t need the startChat value anymore, so I set it to zero and use it as “flag”.
startChat = 0
I check that "flag" every time a blank line is found. If startChat = 0, then we haven't started a new conversation yet, so we can jump over the lines that set the endChat variable. That way 2 (or more) blank lines don’t cause endChat to get set to "" and cause a Type Mismatch error when TimeValue tries to use it.
In other words, we won’t set a new endChat string or try to calculate an elapsed time until the next startChat string has been set.
Wrapped Chat Lines
Now, as far as chat lines that wrap onto the next row, that should only be an issue if the last line of a conversation wraps. Since we only use the next one (or two) cells after the word "conversation" for startChat and the cell above the first blank line for endChat, wrapped lines in the middle of a session (like the one in your latest example) don't matter.
However, if the wrapped line is the line (or lines) above the blank line, then it is an issue since the code will try to use it to set the endChat variable. Unfortunately, we can't simply ignore those lines because we still need to determine the end time of that conversation.
To deal with that issue, I added an Error Handler and one additional variable.
Originally, the Offset function used -1 as the Row argument to look one Row above the blank line for something to build the endChat string with.
It now uses a variable called upRow so we can tell the code how many Rows the Offset function should use.
The first time the code sees a blank line, upRow = -1 and the Offset looks one row up. If endChat gets sets to something that TimeValue can use, then all is good.
However, if a chat line has wrapped, then endChat might look like “gana pu” which will cause the TimeValue to raise a Type Mismatch error.
Once the error is raised, the code will jumped to the Error Handler routine (errWrappedLine) which will "decrement" the upRow variable and then Resume back at the line that pulls the endChat string.
For example, if upRow now equals -2, then the Offset function will look two rows above the blank line and set endChat again. The Error Handler will repeat this (decrementing upRow) as many times as it takes until TimeValue stops raising the error and we’ve found a valid endChat time.
I hope that makes sense!
Anyway, here’s the new code.
Wait…let me say right here that I’m guessing you are going to find other anomalies in your conversations. There are only so many unique situations we can deal with before it gets to the point where adding more code to handle each one-off issue gets to be more cumbersome than it’s worth – especially when someone is giving up their free time to write the code for you. I’m just sayin’….
Dim lstA_Rw, nxtB_Rw, timeStamp, chatSession, upRow As Integer
Dim startChat, endChat As String
On Error GoTo errWrappedLine
'Clear old total from Column B
'Find Last Row in Column A
lstA_Rw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through entries
For timeStamp = 1 To lstA_Rw + 1
'If entry contains "Conversation" then Next cell is
'Start Time unless the "Time" is actually a "Date"
If Cells(timeStamp, 1) Like "*Conversation*" Then
startChat = Mid(Cells(timeStamp, 1).Offset(1, 0), 2, 7)
'If "Time" is a "Date" then Start Time is in *next* cell
If startChat Like "*/*" Then
startChat = Mid(Cells(timeStamp, 1).Offset(2, 0), 2, 7)
'Increment Row counter for Elasped time list
chatSession = chatSession + 1
'If the entry is empty, then the End Time is in the cell above
If Cells(timeStamp, 1) = "" Then
'If startChat = 0, we haven't started a new Conversation yet
If startChat = 0 Then GoTo TwoBlanks
'Set variable for Row argument of Offset function
upRow = -1
'Set endChat String
endChat = Mid(Cells(timeStamp, 1).Offset(upRow, 0), 2, 7)
'Put elasped time for session in Column B
Cells(chatSession, 2) = TimeValue(endChat) - TimeValue(startChat)
'Clear startChat to use as TwoBlanks Flag
startChat = 0
''Sum the times for all Sessions
'Find Last Row in Column B
nxtB_Rw = Range("B" & Rows.Count).End(xlUp).Row
Cells(nxtB_Rw + 1, 2).Formula = "=SUM(B1:B" & nxtB_Rw & ")"
'Error handler for Wrapped Line
'Decrement Row Argument for Offset function
upRow = upRow - 1
'Try a new endChat time