Hi Joel,

Thanks. Wow. I had not even seen code like yours, before.
It gets real close to the desired results.
It doesn't return results to the summary sheet for the last block of
data
that it collects data from, though. I have tried for hours to modify
your
code to adjust for that but I just can't get it. Your code is at a high
level
I have not got to yet.
Can you modify it a bit to eliminate the extra summary rows caused by
the
areas in column P that don't have formulas?
I made a mistake with writing up the desired outcome by saying that I
wanted
to skip blocks of data that don’t have “net” dollar amounts in column
P. I
now realize that in those instances I would really like the macro to
return
the: symbol, the start date as the top date of the column for its
block, the
end date as the bottom date of its block and the dollar net amount to
be
blank.
I will keep trying on my own and appreciate your help. I will also have
to
do more research to learn about some new concepts that your code has
shown me.
Thank you very much.


joel;534176 Wrote:
the only change I made was to start at row 1 instead of row 2. I also
added a header row to the summary sheet and add code to move through all
the sheets.


The extra rows are included because you don't have formulas in column P
for some of the data. The code is finding Net and then a 2nd Net
without any amounts inbetween. I don't know if this is an error or you
want me to eliminate the extra rows. I can easily make the change but
didn't want to do this unless you agree.

Having a BSEE helps in writing this type of code because it is based on
algorithms that are taught in electrical engineering courses. then it
doesn't hurt to also have a Master in computer science for writing
software. People say my code is eligant!



Enum States
FindNet = 1
FindAmount = 2
End Enum
Sub MakeSummary()

Dim State As States

NewRow = 2
Set Sumsht = Sheets("Summary")



With Sumsht
.Range("A1") = "ID"
.Range("B1") = "Start Date"
.Range("C1") = "End Date"
.Range("D1") = "Net"

End With

For Each OldSht In Sheets
With OldSht
If .Range("A1") = "Ticker" Then
State = FindNet
LastRow = .Range("P" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
Data = .Range("P" & RowCount)

Select Case State

Case FindNet:
If Data = "Net" Then
State = FindAmount
startDate = .Range("B" & (RowCount + 1))
End If

Case FindAmount:
If Data < "" Then
'found first dollar amount
ID = .Range("A" & RowCount)
endDate = .Range("B" & RowCount)
With Sumsht
.Range("A" & NewRow) = ID
.Range("B" & NewRow) = startDate
.Range("C" & NewRow) = endDate
.Range("D" & NewRow) = Data

NewRow = NewRow + 1
End With

State = FindNet
End If
End Select
Next RowCount
End If
End With
Next OldSht
End Sub


--
John Yab
------------------------------------------------------------------------
John Yab's Profile: http://www.thecodecage.com/forumz/me...hp?userid=1074
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146619