vba - 检查字符串中 a-z 的函数

Question

我想要:

• 向下循环特定列H(从H4开始)，并且
• 对于该列中的每个单元格，调用一个函数来查看它是否为 true(然后执行某些操作)或结果是否为 false(执行其他操作)。

我在 Call Isletter 上收到运行时错误 ***属性使用无效***。

Sub IfBlank()

Dim Rng As Range
Dim MyCell As Range
Dim Isletter As Range

Set Rng = Range("H4:H" & Cells(Rows.Count, "H").End(xlUp).Row)

For Each MyCell In Rng
Call Isletter
If Isletter(MyCell.Value) = True Then
'do nothing
End If
If Isletter(MyCell.Value) = False Then
 MyCell.Value = "-"
 End If

 Next MyCell
End Sub

Public Function IsLetter(MyCell As String) As Boolean

    Dim intPos As Integer
    For intPos = 1 To Len(MyCell)
        Select Case Asc(Mid(MyCell, intPos, 1))
            Case 33 To 127
                Isletter = True
            Case Else
                Isletter = False
                Exit For
        End Select
    Next
End Function

Answer 1

下面的代码

• 使用变量数组(比范围循环快得多)来处理 H4:Hx 中的每个值
• 使用 regexp可以快速检查字符串中是否至少有一个字符是字母。如果是这样，则将该字符串留空。

这行 Range("H4:H"& Cells(Rows.Count, "H").End(xlUp).Row).Value2 = X 将更改后的数组写回范围.

代码

Sub IfBlank()

Dim rng1 As Range
Dim X
Dim lngCnt As Long

Dim objRegex As Object
X = Range("H4:H" & Cells(Rows.Count, "H").End(xlUp).Row).Value2

Set objRegex = CreateObject("vbscript.regexp")

    With objRegex
         .IgnoreCase = True
         .Pattern = "[a-z]"

         For lngCnt = 1 To UBound(X)
            If .test(X(lngCnt, 1)) Then X(lngCnt, 1) = vbNullString
         Next
    End With

Range("H4:H" & Cells(Rows.Count, "H").End(xlUp).Row).Value2 = X

End Sub