I am not an Excel person but have a VB6 routine which writes data to an
Excel sheet and then averages the data as below for each state
310: Set oWB = oXL.Workbooks.Open(sMaster)

330: With oWB.Worksheets("Sheet1")
340: .Cells(lExcelRow + 2, 1) = "AVERAGES FOR" ' NAME
350: .Cells(lExcelRow + 2, 2) = sState '
STATE
360: .Cells(lExcelRow + 2, 3) = rsKPI![AvgNetPL] ' NET
P&L
etc, etc
No problem thus far.
I am doing averages in VB because I can't get a formula into Excel using
lExcelRow + 2, 3 notation
After I put the averages in I want to Bold the row (lExcelRow + 2,13) ie the
row with the subtotal

All my attempts thus far have failed, probably because I am recording a
macro in Excel and trying to modify that code.

I'm sure to anyone with a modicum of Excel coding it will be a 10 second
answer.
Any help appreciated

TIA

Ian B

Something like

.Cells(lExcelRow + 3, 1).FormulaR1C1 = "=AVERAGE(R1C1:R10C1)"

which isets the cell to =AVERAGE($A$1:$A$10). You can replace the numbers
with variables, like so

.Cells(lExcelRow + 3, 1).FormulaR1C1 = "=AVERAGE(R" & rowStart & "C1:R!
& rowEnd & "C10)"

--
HTH

Bob Phillips

"Ian B" wrote in message
...
I am not an Excel person but have a VB6 routine which writes data to an
Excel sheet and then averages the data as below for each state
310: Set oWB = oXL.Workbooks.Open(sMaster)

330: With oWB.Worksheets("Sheet1")
340: .Cells(lExcelRow + 2, 1) = "AVERAGES FOR" ' NAME
350: .Cells(lExcelRow + 2, 2) = sState
'
STATE
360: .Cells(lExcelRow + 2, 3) = rsKPI![AvgNetPL] ' NET
P&L
etc, etc
No problem thus far.
I am doing averages in VB because I can't get a formula into Excel using
lExcelRow + 2, 3 notation
After I put the averages in I want to Bold the row (lExcelRow + 2,13) ie
the
row with the subtotal

All my attempts thus far have failed, probably because I am recording a
macro in Excel and trying to modify that code.

I'm sure to anyone with a modicum of Excel coding it will be a 10 second
answer.
Any help appreciated

TIA

Ian B

Forgot the bold

.Cells(lExcelRow + 2, 13).EntireRow.Font.Bold = True

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...

Something like

.Cells(lExcelRow + 3, 1).FormulaR1C1 = "=AVERAGE(R1C1:R10C1)"

which isets the cell to =AVERAGE($A$1:$A$10). You can replace the numbers
with variables, like so

.Cells(lExcelRow + 3, 1).FormulaR1C1 = "=AVERAGE(R" & rowStart &
"C1:R!
& rowEnd & "C10)"

--
HTH

Bob Phillips

"Ian B" wrote in message
...
I am not an Excel person but have a VB6 routine which writes data to an
Excel sheet and then averages the data as below for each state
310: Set oWB = oXL.Workbooks.Open(sMaster)

330: With oWB.Worksheets("Sheet1")
340: .Cells(lExcelRow + 2, 1) = "AVERAGES FOR" ' NAME
350: .Cells(lExcelRow + 2, 2) = sState
'
STATE
360: .Cells(lExcelRow + 2, 3) = rsKPI![AvgNetPL] '
NET
P&L
etc, etc
No problem thus far.
I am doing averages in VB because I can't get a formula into Excel using
lExcelRow + 2, 3 notation
After I put the averages in I want to Bold the row (lExcelRow + 2,13) ie
the
row with the subtotal

All my attempts thus far have failed, probably because I am recording a
macro in Excel and trying to modify that code.

I'm sure to anyone with a modicum of Excel coding it will be a 10 second
answer.
Any help appreciated

TIA

Ian B

Thanks Bob

Perfect result.

Ian B

"Bob Phillips" wrote in message
...
Forgot the bold

.Cells(lExcelRow + 2, 13).EntireRow.Font.Bold = True

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...

Something like

.Cells(lExcelRow + 3, 1).FormulaR1C1 = "=AVERAGE(R1C1:R10C1)"

which isets the cell to =AVERAGE($A$1:$A$10). You can replace the
numbers
with variables, like so

.Cells(lExcelRow + 3, 1).FormulaR1C1 = "=AVERAGE(R" & rowStart &
"C1:R!
& rowEnd & "C10)"

--
HTH

Bob Phillips

"Ian B" wrote in message
...
I am not an Excel person but have a VB6 routine which writes data to
an
Excel sheet and then averages the data as below for each state
310: Set oWB = oXL.Workbooks.Open(sMaster)

330: With oWB.Worksheets("Sheet1")
340: .Cells(lExcelRow + 2, 1) = "AVERAGES FOR" '
NAME
350: .Cells(lExcelRow + 2, 2) = sState
'
STATE
360: .Cells(lExcelRow + 2, 3) = rsKPI![AvgNetPL] '
NET
P&L
etc, etc
No problem thus far.
I am doing averages in VB because I can't get a formula into Excel
using
lExcelRow + 2, 3 notation
After I put the averages in I want to Bold the row (lExcelRow + 2,13)
ie
the
row with the subtotal

All my attempts thus far have failed, probably because I am recording
a
macro in Excel and trying to modify that code.

I'm sure to anyone with a modicum of Excel coding it will be a 10
second
answer.
Any help appreciated

TIA

Ian B

Took two stabs, but made it in the end <vbg

Bob

"Ian B" wrote in message
...
Thanks Bob

Perfect result.

Ian B

"Bob Phillips" wrote in message
...
Forgot the bold

.Cells(lExcelRow + 2, 13).EntireRow.Font.Bold = True

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...

Something like

.Cells(lExcelRow + 3, 1).FormulaR1C1 = "=AVERAGE(R1C1:R10C1)"

which isets the cell to =AVERAGE($A$1:$A$10). You can replace the
numbers
with variables, like so

.Cells(lExcelRow + 3, 1).FormulaR1C1 = "=AVERAGE(R" & rowStart &
"C1:R!
& rowEnd & "C10)"

--
HTH

Bob Phillips

"Ian B" wrote in message
...
I am not an Excel person but have a VB6 routine which writes data to
an
Excel sheet and then averages the data as below for each state
310: Set oWB = oXL.Workbooks.Open(sMaster)

330: With oWB.Worksheets("Sheet1")
340: .Cells(lExcelRow + 2, 1) = "AVERAGES FOR" '
NAME
350: .Cells(lExcelRow + 2, 2) = sState
'
STATE
360: .Cells(lExcelRow + 2, 3) = rsKPI![AvgNetPL] '
NET
P&L
etc, etc
No problem thus far.
I am doing averages in VB because I can't get a formula into Excel
using
lExcelRow + 2, 3 notation
After I put the averages in I want to Bold the row (lExcelRow +
2,13)
ie
the
row with the subtotal

All my attempts thus far have failed, probably because I am
recording
a
macro in Excel and trying to modify that code.

I'm sure to anyone with a modicum of Excel coding it will be a 10
second
answer.
Any help appreciated

TIA

Ian B