代码之家 › 专栏 › 技术社区 › Chris

将所选内容转换为前导零格式

leading-zero vba excel

0

Chris · 技术社区 · 8 年前

我试图构建一个简单的宏,将具有数值的选定范围转换为“0000”格式(例如,50,75888,1000将是00500075,0888,1000),即它拾取每个单元格中的每个值,并将字符串值返回给工作表,然后可以在Excel中进行操作

差不多了(我想……)我只需要$format函数的帮助

Sub LeadingZero()
Dim RngSelected As Range
Dim R As String
Dim RCell As Range
Dim Rrng As Range
Dim RevNum As Long

On Error Resume Next
Set RngSelected = Application.InputBox("Please select a range of cells you want to convert to 0000 format", _
                                          "SelectRng", Selection.Address, , , , , 8)

R = RngSelected.Address
Set Rrng = Range(R)

For Each RCell In Rrng.Cells
    RCell.Value = Format$(RCell, "0000")    'this is the line I want to work!
    'RCell.Value2 = Format$(RCell, "0000")  doesn't seem to work either
    Next RCell
End Sub

谢谢

3 回复 | 直到 8 年前

1

0

Dan Donoghue 8 年前

你是不是特别想格式化?如果您真的想要转换值(对查找等有用),那么这个函数将执行您想要的操作。

Function FourDigitValues(InputString As String)
Dim X As Long, MyArr As Variant
MyArr = Split(InputString, ",")
For X = LBound(MyArr) To UBound(MyArr)
    MyArr(X) = Right("0000" & MyArr(X), 4)
Next
FourDigitValues = Join(MyArr, ",")
End Function

2

0

Assaf 8 年前

Sub LeadingZero()
Dim RngSelected As Range
Dim R As String
Dim RCell As Range
Dim Rrng As Range
Dim RevNum As Long

On Error Resume Next
Set RngSelected = Application.InputBox("Please select a range of cells you want to convert to 0000 format", _
                                          "SelectRng", Selection.Address, , , , , 8)

R = RngSelected.Address
Set Rrng = Range(R)

For Each RCell In Rrng.Cells
    RCell.NumberFormat = "000#"
Next RCell
End Sub

3

0

Chris 8 年前

感谢阿萨夫和丹·多诺霍:

Sub LeadingZero2()
'Takes a range with numbers between 1 and 9999 and changes them to text string with "0000" format

Dim RngSelected As Range
Dim RCell As Range
Dim Rrng As Range

On Error Resume Next
Set RngSelected = Application.InputBox("Please select a range of cells you want to convert to 0000 format", _
                                          "SelectRng", Selection.Address, , , , , 8)

Set Rrng = Range(RngSelected.Address)

For Each RCell In Rrng.Cells
RCell.NumberFormat = "@"

RCell = CStr(Array("000", "00", "0")(Len(RCell) - 1) & RCell)

Next RCell

End Sub