account format in excel

[kerang]

hi there,

Hope that someone could help me on this. I have a problem in creating an accounting report in VB6. My report will look like this

Year Date Amoun
2000 17/02/00 200
18/02/00 300
2001 20/02/01 50
29/03/01 600
30/04/01 700

My database will only have date and amount fields.The problem is how to avoid the Year value from repeating.

 

[Trevor Shuttleworth]

Kerang

assuming your dates are in column B and your amounts are in column C ... and
that the headings are in row 1:

The first date will be in Cell B2, the first amount in Cell C2 ...

in Cell A2, put the formula: =YEAR(A2)

in cell A3, put the formula: =IF(YEAR(B3)<>YEAR(B2),YEAR(B3),"")

Now drag down on the fill handle to repeat the formula

Regards

Trevor

hi there,

Hope that someone could help me on this. I have a problem in creating an

accounting report in VB6. My report will look like this.

Year Date Amount
2000 17/02/00 2000
18/02/00 3000
2001 20/02/01 500
29/03/01 6000
30/04/01 7000

My database will only have date and amount fields.The problem is how to

avoid the Year value from repeating.

 

[kerang]

Hi Trevor

Thanks for the guidance, but i assumed that i have to insert the formula into the excel worksheet. How can i apply it in VB6 anyway? Can i use the same formula maybe something like

wsXl.cells(2,1).FormulaLocal= "="YEAR & cells(2,2)" ----> for cell A
and
wsXl.cells(3,1).formulaLocal = "=if"(YEAR & cells(3,2)<> YEAR & cells(2,2), YEAR & cells(3,2), "" "

----- Trevor Shuttleworth wrote: ----

Keran

assuming your dates are in column B and your amounts are in column C ... an
that the headings are in row 1

The first date will be in Cell B2, the first amount in Cell C2 ..

in Cell A2, put the formula: =YEAR(A2

in cell A3, put the formula: =IF(YEAR(B3)<>YEAR(B2),YEAR(B3),""

Now drag down on the fill handle to repeat the formul

Regard

Trevo

hi there
2000 17/02/00 200
18/02/00 300
2001 20/02/01 50
29/03/01 600
30/04/01 700

avoid the Year value from repeating

 

[Trevor Shuttleworth]

Kerang

I think that would be:

wsXl.Cells(2, 1).FormulaLocal = "=Year(B2)" '----> for cell A2
wsXl.Cells(3, 1).FormulaLocal = "=IF(Year(B3)<>Year(B2),Year(B3),"""")"

Regards

Trevor

Hi Trevor,

Thanks for the guidance, but i assumed that i have to insert the formula

into the excel worksheet. How can i apply it in VB6 anyway? Can i use the
same formula maybe something like;

wsXl.cells(2,1).FormulaLocal= "="YEAR & cells(2,2)" ----> for cell A2
and
wsXl.cells(3,1).formulaLocal = "=if"(YEAR & cells(3,2)<> YEAR &

cells(2,2), YEAR & cells(3,2), "" "

 

[onedaywhen]

To do this, I'd create a hierarchical ADO recordset using
Provider=MSDataShape and the SHAPE syntax e.g. something like:

SHAPE {SELECT Year FROM EarningsHistory} AS Parent
APPEND ({SELECT Year, Date, Amount FROM EarningsHistory} AS Child
RELATE Year TO Year)

The advantage of this approach for VB6 is you can use the hierarchical
recordset as the DataSource property of a Data Report.

--