VBA：复制时删除前面的零

VBA: preceding zeros dropped when copied over

提问by Craig G

I am creating a copy of an Excel file using VBA. In the file, there is a column that includes numbers with preceding zeros. The copy of the file is created, but the data in this column is dropped. I need to keep the values with the preceding zeros. How can I resolve this problem with VBA?

我正在使用 VBA 创建 Excel 文件的副本。在文件中，有一列包含前面带有零的数字。已创建文件副本，但删除此列中的数据。我需要保留前面的零值。如何使用 VBA 解决此问题？

采纳答案by Michael Todd

Convert each cell in that column to a text field prior to exporting it. That should ensure that every character is retained (and not treated like a number, which is what it sounds like is happening).

在导出之前将该列中的每个单元格转换为文本字段。这应该确保保留每个字符（而不是将其视为数字，这听起来像是正在发生的）。

回答by Joe Erickson

The best way is to pre-format the column as Text by setting Range.NumberFormat to "@". This way, if a user edits the cell, the cell will stay as text and maintain it's leading zeros. Here is a VBA example:

最好的方法是通过将 Range.NumberFormat 设置为“@”来将列预先格式化为文本。这样，如果用户编辑单元格，单元格将保留为文本并保持其前导零。这是一个 VBA 示例：

ActiveSheet.Range("C:C").NumberFormat = "@"

ActiveSheet.Range("C:C").NumberFormat = "@"

回答by John M

An additional possibility is to append an apostrphe to each cell. This will treat all the values as text which is useful when different tabs treat common values as text vs number (ie copied in vs calculated).

另一种可能性是为每个单元格附加一个撇号。这会将所有值视为文本，这在不同选项卡将常见值视为文本与数字（即复制与计算）时非常有用。

This is done by using the Chr() function and assigning it the character code 39('):

这是通过使用 Chr() 函数并为其分配字符代码 39(') 来完成的：

For x = 1 to 100
If Sheets(origSheet).Cells(x, "A").Value <> "" Then
            Sheets(origSheet).Cells(x, "A").Value = Chr(39) & Sheets(origSheet).Cells(x, "A").Value
        End If

回答by Mike Woodhouse

Given the accepted answer, it's probably not what you need, but setting a custom number format will also get the preceeding zeroes back into the displayed value.

鉴于接受的答案，它可能不是您所需要的，但设置自定义数字格式也会将前面的零恢复到显示值中。

To show a value with leading zeroes up to 8 digits, for example, set the format to 00000000, then 123 will be displayed as 00000123.

例如，要显示带有最多 8 位前导零的值，将格式设置为 00000000，那么 123 将显示为 00000123。

Both the method here and the format-as-text method will result in cell values that will still work in calculations, although horizontal alignment will be different by default. Note also that, for example, concatenating strings to the values will result in differences:

这里的方法和 format-as-text 方法都会导致单元格值在计算中仍然有效，尽管默认情况下水平对齐方式会有所不同。另请注意，例如，将字符串连接到值将导致差异：

as text: displays 00000123, append "x" to get 00000123x

作为文本：显示 00000123，附加“x”以获得 00000123x

as number with custom format: displays 00000123, append "x" to get 123x, because it's still really a number.

作为具有自定义格式的数字：显示 00000123，附加“x”以获得 123x，因为它仍然是一个数字。

Probably TMI, though!

不过可能是TMI！

回答by Jamie

This is the code I have created to resolve this issue:

这是我为解决此问题而创建的代码：

Public Sub Change_10_Digit()
'----------------------------------------------------------------------------
' Change numeric loan number ot a 10 digit text number
' 2010-05-21 by Jamie Coxe
'
' Note: Insure exracted data Column is formated as text before running macro
'----------------------------------------------------------------------------

        Dim Lastrow As Long
        Dim StartRow As Long
        Dim Col As String
        Dim RowNum As Long
        Dim nCol As String
        Dim Loan As String
        Dim Digit_Loan As String
        Dim MyCell As String
        Dim NewCell As String
        Dim Cell_Len As Long
        Dim MyOption As Long

'----- Set Options -------------------------------------------------------

    MyOption = 2 '1 = place data in new column, 2 = Replace data in cell
    StartRow = 2 'Start processing data at this row (skip header row)
    Col = "B" 'Loan number in this colmun to be changed to 10 digit
    nCol = "G" 'New column to place value (option 1 only)

'----- End Option Setings ------------------------------------------------

    'Get last row
    Lastrow = Range(Col & "65536").End(xlUp).Row

    For RowNum = StartRow To Lastrow
        'Combined Column and Row number to get cell data
        MyCell = Col & RowNum

        'Get data in cell
        Loan = Range(MyCell).Value

        'Change data in cell to 10 digit numeric with leading zeros
        Digit_Loan = Format(Loan, "0000000000")

        If My0ption = 1 Then
            'Option 1 enter value in new cell
            NewCell = nCol & RowNum
            Range(NewCell).Value = Digit_Loan
        Else
            'Option 2 replace value in cell
            Range(MyCell).Value = Digit_Loan
        End If

    Next RowNum

End Sub