我是VBA的新手。我正在做一个项目，该项目将验证输入到工作表中的数据的长度和格式。我将扩展代码以包括对所有潜在标头（电话号码，地址，后缀等）的验证。但是，我在phonenumbers子代码中的这一行代码“ Columns（col）.NumberFormat =“ 0”“上收到运行时错误1004。如果我将该行注释掉，则在下一行会收到相同的错误。寻求帮助/解释为什么会发生以及如何解决。
谢谢！
Option Explicit
Sub DataVerification()
Dim i As Long
Dim rw As Long
Dim col As Long
Dim rng As Range
'Set cell background color to nothing
ActiveSheet.Cells.Interior.ColorIndex = xlNone
'loop through header row A10:F10 to determine which column needs validation
For i = 1 To 6
With Sheets("Sheet2")
If UCase(.Cells(10, i).Value) = "PHONE NUMBERS" Then
Call PhoneNumbers
ElseIf UCase(.Cells(10, i).Value) = "ADDRESSES" Then
Call Addresses
ElseIf UCase(.Cells(10, i).Value) = "SUFFIXES" Then
Call Suffixes
Else
MsgBox ("No data entered")
Exit For
End If
End With
Next i
'Phone numbers text found?
If col = 0 Then
MsgBox "Phone Numbers Header not found"
Exit Sub
End If
'Set column format to number
Columns(col).NumberFormat = "0"
'set up the start range, loop until we find an empty cell
'tidy up
Set rng = Nothing
End Sub
Sub PhoneNumbers()
Dim i As Long
Dim rw As Long
Dim col As Long
Dim rng As Range
col = i
'Set column format to number
Columns(col).NumberFormat = "0"
Set rng = Sheets("Sheet2").Cells(11, col)
Do Until rng = ""
If Not IsNumeric(rng.Value) Or Len(rng.Value) <> 11 Then
'highlight cell
rng.Interior.ColorIndex = 3 'red
End If
'get next row
Set rng = rng.Offset(1, 0)
Loop
End Sub
在您的代码中Col为零。列从索引1开始，请尝试以下操作：
Col = 1
'Set column format to number
Columns(col).NumberFormat = "0"
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句