CODEWITHSUNDEEP
excelvba
FrenchConnections
FrenchConnections

Reputation: 391

Loop Through every cell in a range to make sure they are the same length

I am trying to run a test to make sure that every cell in a range of cells has the same length string inside it. Basically, I want to check if every cell in a defined range has a string length of 1. If every cell is 1, then I will do something to the code, else I will do something else. I'm using an evaluate(sumproduct(length()) construct but I am getting a type mismatch. Any help would be appreciated.

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

Upvotes: 2

Views: 1421

Answers (4)

user4039065
user4039065

Reputation:

It seems to me that you are looking for a TRUE/FALSE to a single definitive question; 'Do all of the cells in the range contain values that are the same length?'

If this is the case, then iterating through the values is only right if you store a length value and keep comparing that to all subsequent length values. Also, iterating through all of the cells is going to be slower than a bulk operation.

Sub Name_Length()
    Dim aCells As Range
    Dim LastRow As Long
    Dim NameLengthResult As Double
    Dim bAllTheSameLength As Boolean

    With Sheets("Data Input")
        LastRow = .Cells(Rows.Count, "E").End(xlUp).Row
        Set aCells = .Range("E4:E" & LastRow)
        NameLengthResult = CDbl(Evaluate("SUMPRODUCT(LEN(" & aCells.Address(external:=True) & "))") / aCells.Count)
        bAllTheSameLength = CBool(Round(NameLengthResult, 14) = Len(aCells.Cells(1, 1).Value2))

        If bAllTheSameLength Then
            MsgBox "They are all the same." & Chr(10) & NameLengthResult & " characters wide"
        Else
            MsgBox "They are NOT all the same."
        End If
    End With
End Sub

That will determine whether all cells contain the same number of characters. Be particularly careful with dates as the worksheet LEN function will return 5 as the length of a current date (today is 42191).

If all cells in the range contain values with the same number of characters, you can then use either NameLengthResult or the length of any cell in the range to determine the actual length.

Upvotes: 0

bjaegers
bjaegers

Reputation: 56

You do need to concatenate it out cell.address into your evaluate function so it looks like what nwhaught said:

NameResult = Evaluate("=SUMPRODUCT(LEN(" & cell.address & "))")

However, what you have may not return what you are wanting because it will only do what you would want if the last cell is greater than or equal to 1 character in length. To correct this, possibly try something like this:

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

Set cell = ws.Range("E4:E" & LastRow)
NameResult = Evaluate("=SUMPRODUCT(LEN(" & cell.address & "))")

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

End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166401

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

Upvotes: 2

nwhaught
nwhaught

Reputation: 1592

Two issues...

  • You've passed a variable name referencing a range (cell) as an address reference. It is not. cell.address, however IS a address reference.
  • You've passed a variable name in as part of a string. It needs to be broken out and have its value concatenated into the string.

NameResult = Evaluate("=SUMPRODUCT(LEN(" & cell.address & "))")

Upvotes: 0

Related Questions