excel - 循环遍历范围内的每个单元格以确保它们的长度相同

Question

我正在尝试运行测试以确保一系列单元格中的每个单元格内部都有相同长度的字符串。基本上，我想检查定义范围内的每个单元格的字符串长度是否为 1。如果每个单元格都是 1，那么我将对代码执行一些操作，否则我将执行其他操作。我正在使用 evaluate(sumproduct(length()) 构造，但出现类型不匹配。任何帮助将不胜感激。

Sub Name_Length()

Dim aCell As Range
Dim ws As Worksheet
Dim LastRow As Long
Dim NameStr As String
Dim Namer As Range
Dim NameResult As Integer
Dim i As Integer
Dim cell As Range

Set ws = Sheets("Data Input")

LastRow = ws.Cells(Rows.Count, "E").End(xlUp).Row

For Each cell In ws.Range("E4:E" & LastRow)
NameResult = Evaluate("=SUMPRODUCT(LEN(cell))")
Next cell

If NameResult >= 1 Then
 'Do something'
Else
 MsgBox NameResult
End If

End Sub

Answer 1

Sub Name_Length()

Dim ws As Worksheet
Dim LastRow As Long
Dim addr As String

    Set ws = Sheets("Data Input")
    LastRow = ws.Cells(Rows.Count, "E").End(xlUp).Row
    addr = "E4:E" & LastRow

    'Note `ws.Evaluate` - otherwise the context used will be the
    '   ActiveSheet, which may not be what you want...
    If ws.Evaluate("SUM(1*(LEN(" & addr & ")=1))=rows(" & addr & ")") Then
        Debug.Print "all length=1"
    Else
        Debug.Print "Not all length=1"
    End If

End Sub