Center Across Selection problem in excel 2000

[rschneeg]

I have a macro that I recorded and used in my vb code. It does a center across selection for 3 continguous cells after "combining" the contents of all 3 cells into the first cell separated by a space. Then the values all but the first cell is deleted.
The code works perfectly in excel 2003. I've stored it in my personal.xls.
When copying the same code to an excel user with version 2000 the code doesn't work. It does combine all cell contents into the first cell but it doesn't perform the center across selection.

I recorded the macro in both 2000 and 2003 and the only difference was the .ReadingOrder = xlContext.

I removed this but the same results.

r is the value of the current row.


Here's my code.
If Cells(r, 4).Value <> "" Then
Cells(r, 4).Value = Cells(r, 4).Value & " " & Cells(r, 5).Value & " " & Cells(r, 6).Value
Cells(r, 5).Value = ""
Cells(r, 6).Value = "" 'dont do if blank milestone
myrange = "D" & r & ":" & "F" & r 'range uses row/column letters
Range(myrange).Select
Selection.MergeCells = False 'turn merge off just in case

With Selection
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End If

I'm also experiencing the same problem when trying to do something similar with some date columns but only making them justify right in each column.
here's the code.

myrange = "B" & r & ":" & "C" & r
Range(myrange).Select

With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.NumberFormat = "mm/dd/yy;@"
End With

 

[rschneeg]

The results I am seeing are this:
In the center across selection all the data does get stored in the first cell but it doesn't perform the center across selection.

For the dates, the dates are left justified.

 

[SkipVought]

Hi,

Center across selection is available in xl 2000. Try NOT using the select method...

    Dim r As Long, iCol As Integer
    
    If Cells(r, 4).Value <> "" Then
        For iCol = 5 To 6
            With Cells(r, iCol)
                Cells(r, 4).Value = Cells(r, 4).Value & " " & .Value
                .ClearContents
            End With
        Next

        With Range(Cells(r, 4), Cells(r, 6))
            .HorizontalAlignment = xlCenterAcrossSelection
            .VerticalAlignment = xlTop
            .WrapText = True
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
    End If

On your second question, you have two columns selected, but NOT merged or centered across selection, so the DATE will justify within the column it resides, ONLY.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rschneeg]

works for me (excel 2003) but doesn't work in excel 2000.

To export the code from my PC to another with 2000, I copy the macro to a word doc on a shared drive. The other PC user copies this and pastes over the previous macro in her personal.xls. Then runs the macro button for this.
Same results. Everything adjusts left for her instead on center on selection.

Also, being new to VB, I don't under stand how the code works.

With Cells(r, iCol)
Cells(r, 4).Value = Cells(r, 4).Value & " " & .Value
.ClearContents

I know it start with the 5th column but I am unclear about the assignments statement for cells (r,4).value. Are you trying to clear columns 5 and 6?

As for the other code, I am selecting 2 continuous celkls that both have dates inthem. i want to align each of them top, right in their respective cells. The results on excel 200 are top,left. Works Ok on excel 2003.

Thanks for the help.

 

[SkipVought]

With Cells(r, iCol)
  Cells(r, 4).Value = Cells(r, 4).Value & " " & .Value
  .ClearContents
End With

Second statement concatenates the value in column 4 with a SPACE and the value in iCol.
Third statement clears the contents (value) in iCol.

Programmers look for iterative processes and generalization where it makes sense.

In this case, the bounds of the loop can easily be changed without having to change a bunch of code.

"Everything adjusts left for her instead on center on selection."

Try executing the xlCenterAcrossSelection TWICE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rschneeg]

Running the code to center across selection twice did not work either.
Where I am using center across selection works under excel 2003 but not excel 2000.

Also where I was right justifying the dates, that also does not appear to work. It keeps them left justified on excel 2000.

Is there any problems with using some of the code I developed under excel 2003 and deploying to PCs that use excel 2000?

I'll see if i can do some other things working directly with the excel 2000 PC. We have mixed environment so I need to develop for both.

Thanks for the assistance.

 

[PHV]

We have mixed environment so I need to develop for both
I'd develop in the older environment ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886