VBA Warning - Character Length

[neilwakefield]

Hi Guys,

I was wondering if someone could help me please?

I have a macro that is imports data from a txt file.

I'm trying to write the VBA that would notify a user via a Message Box/Popup Warning if the characters of a cell exceeded the max limit of 32767, and possibly cell reference the cell in question.

I was previously using the following formula in the Conditional Formatting however as the size of the file has increased a popup warning would be more beneficial no:

=LEN(A1)>32766

 

[HackSlash]

I'm afraid you would have to call a sub that evaluates your range of concern.

Try this code and modify the range to fit your dataset:

For Each cell In Range("A1:A5")
    If Len(cell.Text) > 32766 Then MsgBox "characters of a cell exceeded the max limit of 32767" & vbCrLf & "CELL ADDRESS: " & cell.Address
Next

 

[tonyyy]

If there are multiple cells that exceed the maximum character limit, the following will present a single message box rather than one for each cell. It will also highlight the cells in yellow.

Sub CharacterLimit()
Dim txt As String
Dim r As Range, rng As Range
Set rng = Range("A1:A5")
For Each r In rng
    If Len(r.Text) > 32766 Then
        r.Interior.Color = 65535
        txt = txt & vbCrLf & r.Address(0, 0)
    End If
Next r
MsgBox "Cell(s) that exceeded character limits: " & vbCrLf & txt
End Sub

Cheers,

tonyyy