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