|
Bolding Row from VB6
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 |
Bolding Row from VB6
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 |
Bolding Row from VB6
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 |
Bolding Row from VB6
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 |
Bolding Row from VB6
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 |
| All times are GMT +1. The time now is 02:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com