Hi there,
Currently I have the following macro that I will run after extractin
sorted result from some external software.
This macro will lookup certain text value from certain cell and perfor
some simple adjustment on the worksheet and finally adding page brea
and print function so that each fund will be on a fresh page durin
printing

e.g Sub GroupTrade()

Set Trx = Range("D5")
Set FUND = Range("A5")

Do While Not IsEmpty(Trx)

Set nextFUND = FUND.Offset(1, 0)
Set nextTrx = Trx.Offset(1, 0)

If nextTrx.Value Like "Count*" Then
Trx.Offset(1, 0).EntireRow.Font.Bold = True
Trx.Offset(2, 0).EntireRow.Insert Shift:=xlDown
Trx.Offset(2, 0).EntireRow.Font.Size = 20

Set nextTrx = Trx.Offset(3, 0)
Set nextFUND = FUND.Offset(3, 0)

If nextTrx.Value Like "FUND*" Then
Trx.Offset(3, 0).EntireRow.Font.Bold = True
Trx.Offset(3, -3).Value = RTrim(nextFUND) & " : " & Mid(nextTrx
8, 6) & " trades"
Range(Trx.Offset(3, -2), Trx.Offset(3, 1)).Select
Selection.ClearContents
Range(Trx.Offset(3, -3), Trx.Offset(3, 1)).Select
Selection.ClearFormats
With Selection
.Font.Size = 10
.Font.Underline = False
.WrapText = True
.Orientation = 0
.RowHeight = 30
.VerticalAlignment = xlBottom
.ShrinkToFit = False
.Borders(xlEdgeBottom).Weight = xlHairline
.MergeCells = True
End With

Set nextTrx = Trx.Offset(4, 0)
Set nextFUND = FUND.Offset(4, 0)
Set Anymore = Trx.Offset(5, 0)

If Not IsEmpty(Anymore) Then
ActiveWindow.SelectedSheets.HPageBreaks.Ad
Befo=nextTrx
End If

End If
End If

Set Trx = nextTrx
Set FUND = nextFUND

Loop

End Sub

However, I need help as in currently I wanted to set one conditio
whereby, when there's a condition that match the 'Fund*' condition,
will like to copy the name of the fund and paste it at the first empt
cell from the top of the worksheet.

thus the eventual print report will be pages of different fund name a
the said cell.

pls help thk

--
Message posted from http://www.ExcelForum.com

ANyone can assist??

--
Message posted from http://www.ExcelForum.com

I'm not sure what "first empty cell from the top of the worksheet" means.

But if I want to put something in the first empty cell in column X, I'd do
something like:

If nextTrx.Value Like "FUND*" Then

If secondcondition = True Then
If IsEmpty(Range("x1")) Then
Set destcell = Range("x1")
ElseIf IsEmpty(Range("x2")) Then
Set destcell = Range("x2")
Else
Set destcell = Range("x2").End(xlDown).Offset(1, 0)
End If
destcell.Value = whatgoeshere
End If

End If

But this just builds a list in column X.

"keith2816 <" wrote:

Hi there,
Currently I have the following macro that I will run after extracting
sorted result from some external software.
This macro will lookup certain text value from certain cell and perform
some simple adjustment on the worksheet and finally adding page break
and print function so that each fund will be on a fresh page during
printing

e.g Sub GroupTrade()

Set Trx = Range("D5")
Set FUND = Range("A5")

Do While Not IsEmpty(Trx)

Set nextFUND = FUND.Offset(1, 0)
Set nextTrx = Trx.Offset(1, 0)

If nextTrx.Value Like "Count*" Then
Trx.Offset(1, 0).EntireRow.Font.Bold = True
Trx.Offset(2, 0).EntireRow.Insert Shift:=xlDown
Trx.Offset(2, 0).EntireRow.Font.Size = 20

Set nextTrx = Trx.Offset(3, 0)
Set nextFUND = FUND.Offset(3, 0)

If nextTrx.Value Like "FUND*" Then
Trx.Offset(3, 0).EntireRow.Font.Bold = True
Trx.Offset(3, -3).Value = RTrim(nextFUND) & " : " & Mid(nextTrx,
8, 6) & " trades"
Range(Trx.Offset(3, -2), Trx.Offset(3, 1)).Select
Selection.ClearContents
Range(Trx.Offset(3, -3), Trx.Offset(3, 1)).Select
Selection.ClearFormats
With Selection
Font.Size = 10
Font.Underline = False
WrapText = True
Orientation = 0
RowHeight = 30
VerticalAlignment = xlBottom
ShrinkToFit = False
Borders(xlEdgeBottom).Weight = xlHairline
MergeCells = True
End With

Set nextTrx = Trx.Offset(4, 0)
Set nextFUND = FUND.Offset(4, 0)
Set Anymore = Trx.Offset(5, 0)

If Not IsEmpty(Anymore) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add
Befo=nextTrx
End If

End If
End If

Set Trx = nextTrx
Set FUND = nextFUND

Loop

End Sub

However, I need help as in currently I wanted to set one condition
whereby, when there's a condition that match the 'Fund*' condition, I
will like to copy the name of the fund and paste it at the first empty
cell from the top of the worksheet.

thus the eventual print report will be pages of different fund name at
the said cell.

pls help thks

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

hi dave,

What I meant was that I have the following data in excel format, fo
example (the data is extracted by software and thus by default th
'count' and 'fund' is on the same column as Trx.No.

*Daily Report*
Fund TrxNO. Units etc...........
Superfund 12345 300 etc.........
count:1 300
Superfund fund: 1 300

hellofund2 123456 100
hellofund2 123457 200
count:2 300
hellofund2 fund: 2 300

The said data is sorted by fund name and my previous macro wil
actually peform some function after which it will 'page break' afte
the occurrence of the word "Fund" in the Trx No. column.
The purpose of the break is so that i can have different fund on
fresh sheet of paper.

What I wanted to do is to have those reports to be able to print th
fund name after the heading , in this case "Daily Report".
As there's a page break at "fund" level, I will need something tha
will be able to insert at the right place...
thks..

--
Message posted from http://www.ExcelForum.com

If your data is laid out nicely, maybe you can use use .offset to find the name
of the fund and plop it next to the cell (or even within the cell) that is after
the page break.

I didn't understand the layout of your example, but something like this:

If nextTrx.Value Like "FUND*" Then
nexttrx.value = nexttrx.value & "--" & nexttrx.offset(3,2).value
...

Change (3,2) to go down/over as required.



"keith2816 <" wrote:

hi dave,

What I meant was that I have the following data in excel format, for
example (the data is extracted by software and thus by default the
'count' and 'fund' is on the same column as Trx.No.

*Daily Report*
Fund TrxNO. Units etc...........
Superfund 12345 300 etc.........
count:1 300
Superfund fund: 1 300

hellofund2 123456 100
hellofund2 123457 200
count:2 300
hellofund2 fund: 2 300

The said data is sorted by fund name and my previous macro will
actually peform some function after which it will 'page break' after
the occurrence of the word "Fund" in the Trx No. column.
The purpose of the break is so that i can have different fund on a
fresh sheet of paper.

What I wanted to do is to have those reports to be able to print the
fund name after the heading , in this case "Daily Report".
As there's a page break at "fund" level, I will need something that
will be able to insert at the right place...
thks...

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

HI Dave,

What I need is actually 'go up and not go down' which mean I want th
fund name to be inserted on every page .


U might want to see attached excel to see what i meant...

pls help..

Attachment filename: q&a.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=60890
--
Message posted from http://www.ExcelForum.com

I don't open workbooks.

But you can use offset with negative values to go up or to the left.

activesheet.range("b1").offset(-1,-1)
describes A1.

So once you find that key cell, you can use the offset to get the value and
another offset to plop it in:

If nextTrx.Value Like "FUND*" Then
nexttrx.offset(-1,0).value = nexttrx.offset(3,2).value

(-1,0) means up one row, in the same column.



"keith2816 <" wrote:

HI Dave,

What I need is actually 'go up and not go down' which mean I want the
fund name to be inserted on every page .

U might want to see attached excel to see what i meant...

pls help...

Attachment filename: q&a.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=608901
---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

hi Dave,

I know about using negative values for offset. But my problem is that
as each page is sort by different fund name.How am I going to know
where to insert the fund name for each page before the page break as
per my macro.The fund name will be different on every page.

For example:
Page 1: Daily Report
Test fund 2
Data


Page 2: Daily Report
Test Fund 3
Data


---
Message posted from http://www.ExcelForum.com/

Can't you look for the characters: "daily report".

When you find them, just come down one row and plop the fund name into that
cell.

====
But before you do more coding, there's a feature built into excel.
Data|Subtotals. If you put the fund name on each row, you can put a page break
whenever that column changes fund names.



"keith2816 <" wrote:

hi Dave,

I know about using negative values for offset. But my problem is that
as each page is sort by different fund name.How am I going to know
where to insert the fund name for each page before the page break as
per my macro.The fund name will be different on every page.

For example:
Page 1: Daily Report
Test fund 2
Data

Page 2: Daily Report
Test Fund 3
Data

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Hi Dave,

My "Daily Report" is at the first row of the sheet and I uses pag
sheet to repeat row 1 so the "daily report" will be print on ever
page. I also cannot insert "Daily report " on every new fund as I d
not know how much of the DATA will be extracted. The data from my shee
is extracted from external software.

Thus in this respect, I cant use the subtotal function at all a
there's also count function on my excel sheet extracted from externa
software.

I need some something that will perform the following:
Daily report
Fund name 1
Data..................
(page 1 using page break from current macro)
Daily Report
Fund Name 2
Data................
(Page 2 using page break from current macro)

pls assis

--
Message posted from http://www.ExcelForum.com

I have some idea but not complete cos I do not know all the codes.

I was think to insert the following macro to find the location of th
page break since my current macro will insert page break automaticall
e.g.Sub Check_PageBreak()

Dim i As Integer, BreakType As Integer

BreakType = ActiveCell.EntireRow.PageBreak

If BreakType = xlAutomatic Then
?????

End if

End Sub

But i will need another code to insert the fund name at the top of th
row for each page brea

--
Message posted from http://www.ExcelForum.com

Maybe you can just loop through your pagebreaks:

Dim hPB As HPageBreak
For Each hPB In ActiveSheet.HPageBreaks
'just for testing
'MsgBox hPB.Location.Address
hPB.Location.Offset(1, 0).Value = "Your Fund Variable"
Next hPB



"keith2816 <" wrote:

I have some idea but not complete cos I do not know all the codes.

I was think to insert the following macro to find the location of the
page break since my current macro will insert page break automatically
e.g.Sub Check_PageBreak()

Dim i As Integer, BreakType As Integer

BreakType = ActiveCell.EntireRow.PageBreak

If BreakType = xlAutomatic Then
?????

End if

End Sub

But i will need another code to insert the fund name at the top of the
row for each page break

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson