Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't get data centred across pairs of cells...?
Hi folks.
Apologies for double-posting. I've asked this on the vb.generaldiscussion group as well, since my app resides in VB6. Maybe Excel gurus will know a little more about the behaviour I'm seeing. I'm using VB with a reference to the Excel object model, so that my VB app can build Excel workbooks 'on-the-fly'. I have a sub for building a table. Code follows. I hope it's clear what I intend it to do. (I've cut away some of the calls for simplicity - you don't need to see the stuff for changing fonts and applying borders.) Private Sub BuildP7Table(ByVal Sheet As Excel.Worksheet, ByVal TopRow As Integer) Dim strFormula As String Dim intLoop As Integer strFormula = "=IF(R[-2]C="""","""",IF(R[-1]C=""0"",""0%"",R[-2]C/R[-1]C))" With Sheet .Range("C" & CStr(TopRow + 1) & ":J" & CStr(TopRow + 2)).NumberFormat = "@" .Range("C" & CStr(TopRow + 3) & ":J" & CStr(TopRow + 3)).NumberFormat = "0%" For intLoop = 0 To 3 .Range("C" & CStr(TopRow + intLoop) & ":D" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("E" & CStr(TopRow + intLoop) & ":F" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("G" & CStr(TopRow + intLoop) & ":H" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("I" & CStr(TopRow + intLoop) & ":J" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection Next intLoop .Range("A" & CStr(TopRow + 3) & ":B" & CStr(TopRow + 3)).HorizontalAlignment = xlCenterAcrossSelection .Cells(TopRow, 3).FormulaR1C1 = "Number Waiting" .Cells(TopRow, 5).FormulaR1C1 = "12 Weeks" .Cells(TopRow, 7).FormulaR1C1 = "18 Weeks" .Cells(TopRow, 9).FormulaR1C1 = "22+ Weeks" .Cells(TopRow + 1, 1).FormulaR1C1 = .Name .Cells(TopRow + 2, 1).FormulaR1C1 = "Grand Total (32 services)" .Cells(TopRow + 3, 1).FormulaR1C1 = "%" .Cells(TopRow + 3, 3).FormulaR1C1 = strFormula .Cells(TopRow + 3, 5).FormulaR1C1 = strFormula .Cells(TopRow + 3, 7).FormulaR1C1 = strFormula .Cells(TopRow + 3, 9).FormulaR1C1 = strFormula End With End Sub OK, I run the app, and then open the workbook it just built. I pick one of the worksheets, scroll down to where TopRow happened to be for that sheet, and I find that although the 4 text strings written to cells in TopRow are correctly centered (centred here in UK!), the other ranges I specified as xlCenterAcrossSelection are not. If I put the cursor in cell (TopRow + 1, 3), and begin to type something, it appears centered across the range of cells between that cell and (TopRow + 1, 10). In fact, I can do the same in any cell on that row, up to column 10, and the selection seems to be between the chosen cell and the cell in column 10. Actually, I don't need any formatting for the cells in even-numbered columns, because they'll never be visible. So why has the code formatted them? (I can hear you saying 'What if the user selects one of those cells and enters something in it?'. He won't. A later step in the same application comes along and populates everything.) I get the same behaviour in cells on row (TopRow + 2), but oddly, those on row (TopRow + 3) work as I intended. So I've tried making sure all the cells on TopRow + 1 and TopRow + 2 have the same format as TopRow + 3 before setting the horizontal alignment, then putting them back to how I need them afterwards. No joy. I also tried putting something into the offending cells with Formular1c1 beforehand, then removing it afterwards. Nope, that didn't work either. What's going on here? Thanks for any assistance Andy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't get data centred across pairs of cells...?
I can't find any help for XLCenterAcrossSelection, so I tried it this way
With .Range("C" & CStr(TopRow + intLoop) & ":D" & _ CStr(TopRow + intLoop)) .Merge .HorizontalAlignment = xlCenter End With and it seemed to work. "Andy" wrote: Hi folks. Apologies for double-posting. I've asked this on the vb.generaldiscussion group as well, since my app resides in VB6. Maybe Excel gurus will know a little more about the behaviour I'm seeing. I'm using VB with a reference to the Excel object model, so that my VB app can build Excel workbooks 'on-the-fly'. I have a sub for building a table. Code follows. I hope it's clear what I intend it to do. (I've cut away some of the calls for simplicity - you don't need to see the stuff for changing fonts and applying borders.) Private Sub BuildP7Table(ByVal Sheet As Excel.Worksheet, ByVal TopRow As Integer) Dim strFormula As String Dim intLoop As Integer strFormula = "=IF(R[-2]C="""","""",IF(R[-1]C=""0"",""0%"",R[-2]C/R[-1]C))" With Sheet .Range("C" & CStr(TopRow + 1) & ":J" & CStr(TopRow + 2)).NumberFormat = "@" .Range("C" & CStr(TopRow + 3) & ":J" & CStr(TopRow + 3)).NumberFormat = "0%" For intLoop = 0 To 3 .Range("C" & CStr(TopRow + intLoop) & ":D" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("E" & CStr(TopRow + intLoop) & ":F" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("G" & CStr(TopRow + intLoop) & ":H" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("I" & CStr(TopRow + intLoop) & ":J" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection Next intLoop .Range("A" & CStr(TopRow + 3) & ":B" & CStr(TopRow + 3)).HorizontalAlignment = xlCenterAcrossSelection .Cells(TopRow, 3).FormulaR1C1 = "Number Waiting" .Cells(TopRow, 5).FormulaR1C1 = "12 Weeks" .Cells(TopRow, 7).FormulaR1C1 = "18 Weeks" .Cells(TopRow, 9).FormulaR1C1 = "22+ Weeks" .Cells(TopRow + 1, 1).FormulaR1C1 = .Name .Cells(TopRow + 2, 1).FormulaR1C1 = "Grand Total (32 services)" .Cells(TopRow + 3, 1).FormulaR1C1 = "%" .Cells(TopRow + 3, 3).FormulaR1C1 = strFormula .Cells(TopRow + 3, 5).FormulaR1C1 = strFormula .Cells(TopRow + 3, 7).FormulaR1C1 = strFormula .Cells(TopRow + 3, 9).FormulaR1C1 = strFormula End With End Sub OK, I run the app, and then open the workbook it just built. I pick one of the worksheets, scroll down to where TopRow happened to be for that sheet, and I find that although the 4 text strings written to cells in TopRow are correctly centered (centred here in UK!), the other ranges I specified as xlCenterAcrossSelection are not. If I put the cursor in cell (TopRow + 1, 3), and begin to type something, it appears centered across the range of cells between that cell and (TopRow + 1, 10). In fact, I can do the same in any cell on that row, up to column 10, and the selection seems to be between the chosen cell and the cell in column 10. Actually, I don't need any formatting for the cells in even-numbered columns, because they'll never be visible. So why has the code formatted them? (I can hear you saying 'What if the user selects one of those cells and enters something in it?'. He won't. A later step in the same application comes along and populates everything.) I get the same behaviour in cells on row (TopRow + 2), but oddly, those on row (TopRow + 3) work as I intended. So I've tried making sure all the cells on TopRow + 1 and TopRow + 2 have the same format as TopRow + 3 before setting the horizontal alignment, then putting them back to how I need them afterwards. No joy. I also tried putting something into the offending cells with Formular1c1 beforehand, then removing it afterwards. Nope, that didn't work either. What's going on here? Thanks for any assistance Andy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't get data centred across pairs of cells...?
Thanks Barb, that's solved the problem. It also simplifies the code for
putting borders round the cells. :-) Andy "Barb Reinhardt" wrote: I can't find any help for XLCenterAcrossSelection, so I tried it this way With .Range("C" & CStr(TopRow + intLoop) & ":D" & _ CStr(TopRow + intLoop)) .Merge .HorizontalAlignment = xlCenter End With and it seemed to work. "Andy" wrote: Hi folks. Apologies for double-posting. I've asked this on the vb.generaldiscussion group as well, since my app resides in VB6. Maybe Excel gurus will know a little more about the behaviour I'm seeing. I'm using VB with a reference to the Excel object model, so that my VB app can build Excel workbooks 'on-the-fly'. I have a sub for building a table. Code follows. I hope it's clear what I intend it to do. (I've cut away some of the calls for simplicity - you don't need to see the stuff for changing fonts and applying borders.) Private Sub BuildP7Table(ByVal Sheet As Excel.Worksheet, ByVal TopRow As Integer) Dim strFormula As String Dim intLoop As Integer strFormula = "=IF(R[-2]C="""","""",IF(R[-1]C=""0"",""0%"",R[-2]C/R[-1]C))" With Sheet .Range("C" & CStr(TopRow + 1) & ":J" & CStr(TopRow + 2)).NumberFormat = "@" .Range("C" & CStr(TopRow + 3) & ":J" & CStr(TopRow + 3)).NumberFormat = "0%" For intLoop = 0 To 3 .Range("C" & CStr(TopRow + intLoop) & ":D" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("E" & CStr(TopRow + intLoop) & ":F" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("G" & CStr(TopRow + intLoop) & ":H" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection .Range("I" & CStr(TopRow + intLoop) & ":J" & CStr(TopRow + intLoop)).HorizontalAlignment = xlCenterAcrossSelection Next intLoop .Range("A" & CStr(TopRow + 3) & ":B" & CStr(TopRow + 3)).HorizontalAlignment = xlCenterAcrossSelection .Cells(TopRow, 3).FormulaR1C1 = "Number Waiting" .Cells(TopRow, 5).FormulaR1C1 = "12 Weeks" .Cells(TopRow, 7).FormulaR1C1 = "18 Weeks" .Cells(TopRow, 9).FormulaR1C1 = "22+ Weeks" .Cells(TopRow + 1, 1).FormulaR1C1 = .Name .Cells(TopRow + 2, 1).FormulaR1C1 = "Grand Total (32 services)" .Cells(TopRow + 3, 1).FormulaR1C1 = "%" .Cells(TopRow + 3, 3).FormulaR1C1 = strFormula .Cells(TopRow + 3, 5).FormulaR1C1 = strFormula .Cells(TopRow + 3, 7).FormulaR1C1 = strFormula .Cells(TopRow + 3, 9).FormulaR1C1 = strFormula End With End Sub OK, I run the app, and then open the workbook it just built. I pick one of the worksheets, scroll down to where TopRow happened to be for that sheet, and I find that although the 4 text strings written to cells in TopRow are correctly centered (centred here in UK!), the other ranges I specified as xlCenterAcrossSelection are not. If I put the cursor in cell (TopRow + 1, 3), and begin to type something, it appears centered across the range of cells between that cell and (TopRow + 1, 10). In fact, I can do the same in any cell on that row, up to column 10, and the selection seems to be between the chosen cell and the cell in column 10. Actually, I don't need any formatting for the cells in even-numbered columns, because they'll never be visible. So why has the code formatted them? (I can hear you saying 'What if the user selects one of those cells and enters something in it?'. He won't. A later step in the same application comes along and populates everything.) I get the same behaviour in cells on row (TopRow + 2), but oddly, those on row (TopRow + 3) work as I intended. So I've tried making sure all the cells on TopRow + 1 and TopRow + 2 have the same format as TopRow + 3 before setting the horizontal alignment, then putting them back to how I need them afterwards. No joy. I also tried putting something into the offending cells with Formular1c1 beforehand, then removing it afterwards. Nope, that didn't work either. What's going on here? Thanks for any assistance Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match pairs of date in cells | Excel Discussion (Misc queries) | |||
Match pairs of data in cells | Excel Discussion (Misc queries) | |||
help with Conditional formatting pairs of cells | Excel Worksheet Functions | |||
Entering left & right aligned, & centred text, into one merged cel | New Users to Excel | |||
Working with pairs of cells | Excel Worksheet Functions |