vba 在我选择的列上运行的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18361467/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Macro to run on the column I have selected
提问by user2119980
I have the following macro, it adds a number of zeroes to the beginning of a string of numbers until the number has a total of 7 digits. Currently it only does the A column, I would like for it to run the macro for whichever column I have selected so I do not always have to cut and paste and recut and paste all the columns I need to run it on. ANy ideas?
我有以下宏，它在一串数字的开头添加许多零，直到数字总共有 7 位数字。目前它只处理 A 列，我希望它为我选择的任何列运行宏，这样我就不必总是剪切和粘贴并重新剪切和粘贴运行它所需的所有列。有任何想法吗？
Sub AddZeroes1()
'Declarations
Dim cl As Range
Dim i As Long, endrow As Long
Application.ScreenUpdating = False
'Converts the A column format to Text format
Columns("A:A").NumberFormat = "@"
'finds the bottom most row
endrow = ActiveSheet.Range("A1048576").End(xlUp).Row
'## Or, for Excel 2003 and prior: ##'
'endrow = ActiveSheet.Range("A65536").End(xlUp).Row
'loop to move from cell to cell
For i = 1 To endrow - 1
Set cl = Range("A" & i)
With cl
'The Do-While loop keeps adding zeroes to the front of the cell value until it hits a length of 7
Do While Len(.Value) < 7
.Value = "0" & .Value
Loop
End With
Next i
Application.ScreenUpdating = True
End Sub
回答by Jon Crowell
You can update as many columns as you want by changing the target to the selection rather than a specific column. (as suggested by t.thielemans)
您可以通过将目标更改为选择而不是特定列来更新任意数量的列。（如 t.thielemans 所建议）
Try this:
尝试这个：
Sub AddZeroesToSelection()
Dim rng As Range
Dim cell As Range
Set rng = Selection
rng.NumberFormat = "@"
For Each cell In rng
Do While Len(cell.Value) < 7
cell.Value = "0" & cell.Value
Loop
Next cell
End Sub
回答by tigeravatar
From your question:
从你的问题：
it adds a number of zeroes to the beginning of a string of numbers until the number has a total of 7 digits
它在一串数字的开头添加一些零，直到数字总共有 7 位
If you simply want numbers to show leading 0's until the numbers are 7 digits long, you can use a custom format of: 0000000
如果您只是想让数字显示前导 0 直到数字长度为 7 位，您可以使用自定义格式：0000000
For example:
例如：
123
5432
26
9876543
Select the cells -> right-click -> Format Cells -> Custom -> Type in "0000000" (no quotes) -> OK
选择单元格 -> 右键单击 -> 设置单元格格式 -> 自定义 -> 输入“0000000”（无引号） -> 确定
Now they should appear with the leading 0's:
现在它们应该以 0 开头：
0000123
0005432
0000026
9876543
If it has to be a macro, then this should work:
如果它必须是一个宏，那么这应该有效：
Sub AddZeroes1()
Selection.NumberFormat = "0000000"
End Sub
回答by Gary's Student
Change only the MyCol line:
仅更改 MyCol 行：
Sub AddZeroes1()
Dim cl As Range
Dim i As Long, endrow As Long
Dim MyCol As String
MyCol = "A"
Application.ScreenUpdating = False
Columns(MyCol & ":" & MyCol).NumberFormat = "@"
endrow = ActiveSheet.Range(MyCol & "1048576").End(xlUp).Row
For i = 1 To endrow - 1
Set cl = Range(MyCol & i)
With cl
Do While Len(.Value) < 7
.Value = "0" & .Value
Loop
End With
Next i
Application.ScreenUpdating = True
End Sub
NOT TESTED
未测试
