Macros to count characters

[wilsoch]

I have to find, in a large workbook (12 sheets), every instance where
there are more than 255 characters per cell. I really don't want to
have to use the LEN formula because there are so many cells I need to
check. I'd much rather just write a macros. I'm struggling with how to
do it, though.

Any suggestions?

 

[David McRitchie]

Are they strictly text constants or are they from
formulas or both. Would coloring the cells be
sufficient (wouldn't work if you already have cells
colored directly or with Conditional Formatting),
or do you need a worksheet with the
sheetname and up to 10 cell addresses across row
or sheetname, cell address, first xx bytes of text.

If you can do it yourself there are some examples of
processing each sheet in
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
http://www.mvps.org/dmcritchie/excel/sheets.htm
and if you want to limit checking to certain types of cells
http://www.mvps.org/dmcritchie/excel/proper.htm#specialcells

 

[Guest]

First add a sheet called "answers" and then run:

Sub count_long()
i = 1
For Each sh In Worksheets
sh.Activate
For Each r In ActiveSheet.UsedRange
If Len(r.Value) > 255 Then
Sheets("answers").Cells(i, "A") = sh.Name
Sheets("answers").Cells(i, "B") = r.Address
i = i + 1
End If
Next
Next
End Sub

 

[JE McGimpsey]

One way:

You don't say what "find" means - i.e., find one, stop, then find the
next? Highlight all? I'll assume the latter:

Public Sub Highlight256Plus()
Dim ws As Worksheet
Dim rFound As Range
Dim rCell As Range

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set rFound = ws.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If Not rFound Is Nothing Then
For Each rCell In rFound
With rCell
If Len(.Text) > 255 Then .Interior.ColorIndex = 3
End With
Next rCell
End If
Next ws
End Sub

 

[Dave Peterson]

If you're not using Format|Conditional formatting for anything, you could
highlight the cells using that.

Select all the cells
With A1 the activecell
format|conditional formatting
Formula is:
=len(a1)>255
and give it a nice shade.
(and remove the format|conditional formatting when you're finished????)

If you're not using data|Validation for anything, you could use that.

Select the used range (all the cells may slow down excel too much)
Data|Validation
Settings tab
Allow: Text length
Data: Less than
Maximum: 255

Error Alert tab
Turn off Show error alert after invalid invalid data is entered.

Then View|Toolbars|Show Formula Auditing
Click on the "Circle Invalid data" icon.

Click on the "clear validation circles" when you're finished
(and remove the data|validation rules????)

 

[Dave Peterson]

Ps. I used the xl2003 menu system to describe those techniques.