[已解决]自订函数问题(不足四位,前面固定补零)

lkk0063

是否有什么方法能用VBA方式使 out 内的"BU栏位"前面固定补零
EX:
输入6, 自动显示0006(前面补零)
输入65, 自动显示0065(前面补零)
输入265, 自动显示0265(前面补零)
输入1265, 自动显示1265

最佳答案

月排行榜 / 总排行榜

hasyh2008

2022-6-10 16:10

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 73 And .Row > 5 And .Count = 1 Then
            .Value = "'" & Application.Text(Target, "0000")
        End If
    End With
End Sub

跳转到最佳答案楼层

hasyh2008

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 73 And .Row > 5 And .Count = 1 Then
            .Value = "'" & Application.Text(Target, "0000")
        End If
    End With
End Sub

砂海

hasyh2008 发表于 2022-6-10 16:10
Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 73 And .R ...

Application.Text(
绕路了,

Vba自带:
format(

hasyh2008

砂海 发表于 2022-6-10 18:06
Application.Text(
绕路了,

OK，谢谢指正！

金樽空对月

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. On Error Resume Next
   
3. Application.EnableEvents = False
   
4. If Target.Column <> 73 And Target.Row < 6 Then GoTo 100
   
5. Target.Value = Format(Target.Value, "0000")
   
6. 100:
   
7. Application.EnableEvents = True
   
8. End Sub

复制代码

hasyh2008

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 73 And .Row > 5 Then
            .NumberFormatLocal = "0000"
        End If
    End With
End Sub

用NumberFormatLocal，还是数值；用"'" & application.text(target.value，“0000"),则改为文本格式。用"'" & Vba.format(target.value，“0000")前面也要加"'"。

hasyh2008

砂海 发表于 2022-6-10 18:06
Application.Text(
绕路了,

VBA函数确实比工作表函数快些

Sub 工作表函数()
    Dim Rc As Long, T
    T = Timer
    With ActiveSheet
        For Rc = 1 To 10000
            .Cells(Rc, 1).Value = "'" & Application.Text(.Cells(Rc, 1), "0000")
        Next Rc
        .Cells(Rows.Count, 3).End(xlUp).Offset(1) = VBA.Format(Timer - T, "0.0000")

    End With
End Sub


Sub VBA函数()
    Dim Rc As Long, T
    T = Timer
    With ActiveSheet
        For Rc = 1 To 10000
            .Cells(Rc, 1).Value = "'" & VBA.Format(.Cells(Rc, 1), "0000")
        Next Rc
        .Cells(Rows.Count, 4).End(xlUp).Offset(1) = VBA.Format(Timer - T, "0.0000")
    End With
End Sub