Would someone please help me with a macro? I have data on a sheet that I am
trying to selectively move to a summary sheet; below is an example:

Revenue Net


$1,296.00 $24.00

Revenue Net

$964.00 ($28.00)


Revenue Net
$416.00 ($40.00)
$416.00 ($40.00)
$416.00 ($40.00)

There are blocks of data on a sheet. Each block has a different number of
rows. Each block is seperated by one blank row.
In column P is the heading "Net" in each block. Under "Net" can be blanks or
a dollar amount or the blank between blocks of data or a new heading of "Net"
for a new block of data. I am trying to move the dollar amount to a summary
sheet. Sometimes there is more than one dollar amount in each block... I only
want the first instance of the dollar amount then I need to skip to the next
block and get the first instance of the dollar amount in that next block.
Sometimes there is no dollar amount and then I would have to skip to the next
block of data. When/if I find the dollar amount I need to copy and paste it
to the summary sheet and also copy and past the values in that same row from
column A (an ID) and column B (the end date). The value 2nd from the top of
the column B in that block of data is the "start" date. I need to capture the
start date too and move it to the summary sheet. In summary the macro would
grab 4 bits of data and move it to the summary sheet and then move on to the
next block of data a grab and move 4 bits of data from that block, etc.
I have been working hard with loops and cases and if's for a week and can't
quite get it so any help is very appreciated with big thanks.

--
John Yab

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"John Yab" wrote in message
...
Would someone please help me with a macro? I have data on a sheet that I
am
trying to selectively move to a summary sheet; below is an example:

Revenue Net


$1,296.00 $24.00

Revenue Net

$964.00 ($28.00)


Revenue Net
$416.00 ($40.00)
$416.00 ($40.00)
$416.00 ($40.00)

There are blocks of data on a sheet. Each block has a different number of
rows. Each block is seperated by one blank row.
In column P is the heading "Net" in each block. Under "Net" can be blanks
or
a dollar amount or the blank between blocks of data or a new heading of
"Net"
for a new block of data. I am trying to move the dollar amount to a
summary
sheet. Sometimes there is more than one dollar amount in each block... I
only
want the first instance of the dollar amount then I need to skip to the
next
block and get the first instance of the dollar amount in that next block.
Sometimes there is no dollar amount and then I would have to skip to the
next
block of data. When/if I find the dollar amount I need to copy and paste
it
to the summary sheet and also copy and past the values in that same row
from
column A (an ID) and column B (the end date). The value 2nd from the top
of
the column B in that block of data is the "start" date. I need to capture
the
start date too and move it to the summary sheet. In summary the macro
would
grab 4 bits of data and move it to the summary sheet and then move on to
the
next block of data a grab and move 4 bits of data from that block, etc.
I have been working hard with loops and cases and if's for a week and
can't
quite get it so any help is very appreciated with big thanks.

--
John Yab

I don't have the spreadsheet so there may be an error with the code.
but is is very close. Try this

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

Dim State As States

NewRow = 2
Set SumSht = Sheets("Summary")
Set OldSht = ActiveSheet

State = FindNet

With OldSht
LastRow = .Range("P" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
Data = .Range("P" & RowCount)

Select Case State

Case FindNet:
If Data = "Revenue 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 With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146619

Hi Joel,

Thanks. Wow. I had not even seen code like yours, before.
It gets real close to the desired resullts.
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 dont 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.

--
John Yab


"joel" wrote:


I don't have the spreadsheet so there may be an error with the code.
but is is very close. Try this

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

Dim State As States

NewRow = 2
Set SumSht = Sheets("Summary")
Set OldSht = ActiveSheet

State = FindNet

With OldSht
LastRow = .Range("P" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
Data = .Range("P" & RowCount)

Select Case State

Case FindNet:
If Data = "Revenue 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 With
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146619

.

I added one IF statnement which fixes both problems. Because the code
didn't find an amount the code thought "Net" was the amount and then
started to search for the next Net" . It turned out the data that was
missing amounts was the 2nd to last section of data so it skipped the
last section on the page.

My code has memory in the fact it rembers what data it found and uses
that information in finding the next piece of information. the code
basically remember that it found the word "Net" or found a dollar
amount. If the code finds a dollar amount it does noting (skipping all
the other dollar amount) until it find the word "Net".

I've been writing code like this for over 30 years starting with
FORTRAN amoung other programming languages.


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
If Data < "Net" 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 If
End Select
Next RowCount
End If
End With
Next OldSht
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146619

Hi Joel,
It works. It's cool. Thank you very much.

I tried all evening to make a modification when there isn't €œnet€� dollar
amounts in column P in a block:
to 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 or 0.
I thought if I put in a 0 at the end of column P of those blocks empty of
"net" then your code would return entries to the summary sheet. I haven't got
that mod to work yet, below is the code. Would you be able to help a bit
more? Also where can I learn about: "Enum States", "FindNet = 1", "End Enum"
kind of code... a book you could recomend, maybe?

Thanks so much.



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

Dim State As States

'Delete the sheet "Summary" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a new summary worksheet.
Set Sumsht =
ActiveWorkbook.Worksheets.Add(after:=Worksheets(Wo rksheets.Count))
Sumsht.Name = "Summary"


'Set up titles
'Range("A1:D1") = Array("Symbol", "Start", "End", "Net")
Columns("B:D").HorizontalAlignment = xlRight

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

With Sumsht
..Range("A1") = "Symbol"
..Range("B1") = "Start"
..Range("C1") = "End"
..Range("D1") = "Net"
..Rows("1:1").Font.Bold = True
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:
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
If Data = "" Then
Cells.Offset(-2, 0).Value = "0"
ElseIf Data < "" Then
If Data < "Net" Then
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
'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 If
End Select
Next RowCount
End If
End With
Next OldSht
End Sub
--
John Yab


"joel" wrote:


I added one IF statnement which fixes both problems. Because the code
didn't find an amount the code thought "Net" was the amount and then
started to search for the next Net" . It turned out the data that was
missing amounts was the 2nd to last section of data so it skipped the
last section on the page.

My code has memory in the fact it rembers what data it found and uses
that information in finding the next piece of information. the code
basically remember that it found the word "Net" or found a dollar
amount. If the code finds a dollar amount it does noting (skipping all
the other dollar amount) until it find the word "Net".

I've been writing code like this for over 30 years starting with
FORTRAN amoung other programming languages.


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
If Data < "Net" 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 If
End Select
Next RowCount
End If
End With
Next OldSht
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146619

.

Hi and Thanks Joel and The Code Cage,

I am thrilled with your reply and the results.
The results are very close to perfect... I should have included a bit
more information. I have attached a sample workbook with the last sheet
showing the results from running the macro... very impressive!! I
changed "Revenue Net" to "Net" but I am missing some information at the
top or the bottom of the summary sheet depending on what code I change
and there are a couple of lines in the summary that are titles that I
don't know how to not have copied over. The workbook is acutally many
sheets but I included just a couple to keep it smaller. Each one of the
sheets that has the data to collect has the word "Ticker" in A1 (there
are other sheets too) and I am thinking that I should be able to
probably write the code to cycle through all the "Ticker" worksheets to
have a cumulative summary.

I am super grateful for your help,

Thanks,
John Yab

John Yab;533513 Wrote:
Would someone please help me with a macro? I have data on a sheet that I
am
trying to selectively move to a summary sheet; below is an example:

Revenue Net


$1,296.00 $24.00

Revenue Net

$964.00 ($28.00)


Revenue Net
$416.00 ($40.00)
$416.00 ($40.00)
$416.00 ($40.00)

There are blocks of data on a sheet. Each block has a different number
of
rows. Each block is seperated by one blank row.
In column P is the heading "Net" in each block. Under "Net" can be
blanks or
a dollar amount or the blank between blocks of data or a new heading of
"Net"
for a new block of data. I am trying to move the dollar amount to a
summary
sheet. Sometimes there is more than one dollar amount in each block...
I only
want the first instance of the dollar amount then I need to skip to the
next
block and get the first instance of the dollar amount in that next
block.
Sometimes there is no dollar amount and then I would have to skip to
the next
block of data. When/if I find the dollar amount I need to copy and
paste it
to the summary sheet and also copy and past the values in that same row
from
column A (an ID) and column B (the end date). The value 2nd from the
top of
the column B in that block of data is the "start" date. I need to
capture the
start date too and move it to the summary sheet. In summary the macro
would
grab 4 bits of data and move it to the summary sheet and then move on
to the
next block of data a grab and move 4 bits of data from that block,
etc.
I have been working hard with loops and cases and if's for a week and
can't
quite get it so any help is very appreciated with big thanks.

--
John Yab


+-------------------------------------------------------------------+
|Filename: Position calculator Joel.xlsm |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=341|
+-------------------------------------------------------------------+

--
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

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=146619

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