VBA宏在单元格包含文本时起作用，但如果包含数字则不起作用

Hi I'm currently using a macro that autoformats tables for me and aligns all cells centrally except for the ones in the first selected column.

嗨我正在使用一个为我自动格式化表格的宏,并将所有单元格集中对齐,除了第一个选定列中的单元格。

I was wondering if there was a way to tweak this so that the 1st selected column is aligned left only if it contains text and not if it contains a number

我想知道是否有一种方法可以调整它,以便第一个选定列仅在包含文本时对齐,如果包含数字则不对齐

Here's the code:

这是代码:

Sub Test_align_left()

With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

Selection.Columns(1).Select

On Error Resume Next

With Selection
    .SpecialCells(xlCellTypeConstants, xlTextValues).HorizontalAlignment = xlLeft
    .SpecialCells(xlCellTypeFormulas, xlTextValues).HorizontalAlignment = xlLeft
End With

End Sub

Thanks in advance,

提前致谢,

Thomas

2 个解决方案

#1

If you mean left align if text or centred if numeric then here is a way which avoids looping through each cell.

如果你的意思是左对齐文本或居中如果数字,那么这里是一种避免循环每个单元格的方法。

Sub x()
On Error Resume Next
With Columns(1)
    .SpecialCells(xlCellTypeConstants, xlTextValues).HorizontalAlignment = xlLeft
    .SpecialCells(xlCellTypeConstants, xlNumbers).HorizontalAlignment = xlCenter
    .SpecialCells(xlCellTypeFormulas, xlNumbers).HorizontalAlignment = xlCenter
    .SpecialCells(xlCellTypeFormulas, xlTextValues).HorizontalAlignment = xlLeft
End With

End Sub

#2

If you just want to leave the first column alone you could do something like:

如果您只想离开第一列,您可以执行以下操作:

Sub Test_align_left()

    'Test_align_left Macro

    With Selection.offset(0,1).resize(,Selection.columns.count-1)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

End Sub

#1