Bolding Row from VB6

[Ian B]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
>

[Bob Phillips]

Forgot the bold

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

--
HTH

Bob Phillips

"Bob Phillips" <[email protected]> wrote in message
news:eN%[email protected]...
>
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> >
> >
>
>

[Ian B]

Thanks Bob

Perfect result.

Ian B

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Forgot the bold
>
> .Cells(lExcelRow + 2, 13).EntireRow.Font.Bold = True
>
> --
> HTH
>
> Bob Phillips
>
> "Bob Phillips" <[email protected]> wrote in message
> news:eN%[email protected]...
> >
> > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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
> > >
> > >
> > >
> >
> >
>
>

[Bob Phillips]

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

Bob

"Ian B" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob
>
> Perfect result.
>
> Ian B
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Forgot the bold
> >
> > .Cells(lExcelRow + 2, 13).EntireRow.Font.Bold = True
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:eN%[email protected]...
> > >
> > > 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" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > 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
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>