我做错什么了？VBA如果这个这个然后这个

Question

我真的需要帮助..。

我试着把这段代码做好。我要在邮编的前面放0。仅在空细胞和短于5的细胞中。

For i = 2 To ende2
    If (Not IsEmpty(LTrim(Cells(i, 9).Value))) And Len(LTrim(Cells(i, 9).Value)) < 5 Then
         Cells(i, 9).Value = "0" & Cells(i, 9).Value
    End If
next i

该代码返回邮政编码的0。但是把一个0放进空的细胞里。为什么？

我对编程很陌生。所以，请不要对我太苛刻。

(谢谢你的帮助:)

LG Madosa

Answer 1

LTrim导致值非空，即使它仍然是零长度字符串.试试这个：

If (Not IsEmpty(Cells(i, 9))) And Len(LTrim(Cells(i, 9).Value)) < 5 Then

顺便说一句，如果要添加零的值是数字的，那么程序将没有结果。您需要将单元格格式更改为文本。把这个放在If行后面：

Cells(i, 9).NumberFormat = "@"

Answer 2

你不需要密码。

假设邮政编码在A栏中，则将此公式添加到B栏中。

=IF(A1<>"","0"&A1,"")

下面的分类解释了。

' Checks the cell in A1 for something
    =IF(A1<>"",
' If there is, concatenate a "0" and whatever is in A1
        "0"&A1, 
' Otherwise put an empty string.
        "")

然后，您可以复制B列并选择Edit >粘贴特殊>值来将公式转换为文本。

ALT+E，然后S，V，然后点击OK。

Answer 3

虽然这段代码更长，但它将大大加快速度。

• 忽略要开始的空单元格(使用Set rng1 = Selection.SpecialCells(xlConstants, xlNumbers))
• 它使用变体数组。

代码

Sub AddLeadingZeros()
    Dim rng1 As Range
    Dim rngArea As Range
    Dim strRep As String
    Dim lngRow As Long
    Dim lngCol As Long
    Dim lngCalc As Long
    Dim X()

    strRep = "'0"

    On Error Resume Next
    'Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8)
    Set rng1 = Selection.SpecialCells(xlConstants, xlNumbers)
    If rng1 Is Nothing Then Exit Sub
    On Error GoTo 0

   'Speed up the code by turning off screenupdating and setting calculation to manual
   'Disable any code events that may occur when writing to cells
    With Application
        lngCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    'Test each area in the user selected range

    'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
    For Each rngArea In rng1.Areas
        'The most common outcome is used for the True outcome to optimise code speed
        If rngArea.Cells.Count > 1 Then
           'If there is more than once cell then set the variant array to the dimensions of the range area
           'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
            X = rngArea.Value2
            For lngRow = 1 To rngArea.Rows.Count
                For lngCol = 1 To rngArea.Columns.Count
                    'add leading zeroes
                    If Len(X(lngRow, lngCol)) < 5 Then X(lngRow, lngCol) = strRep & X(lngRow, lngCol)
                Next lngCol
            Next lngRow
            'Dump the updated array swith a leading zeroes back over the initial range
            rngArea.Value2 = X
        Else
            'caters for a single cell range area. No variant array required
            If (Len(rngArea.Value) < 5) Then rngArea.Value = strRep & rngArea.Value2
        End If
    Next rngArea

    'cleanup the Application settings
    With Application
        .ScreenUpdating = True
        .Calculation = lngCalc
        .EnableEvents = True
    End With

  End Sub