erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
Do you know how many caracters can appear in one cell ?
With this macro you can do some testing ...
kind regards to everybody,
Erik
With this macro you can do some testing ...
Code:
Option Explicit
Sub count_caracters_in_cell()
Dim cell As Range
Dim nr As Integer
Dim x As Integer
Dim temp As String
Set cell = ThisWorkbook.Sheets(1).Range("A1")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Do
nr = Application.InputBox("fill in number of caracters to be tested (20,30,...,3000)", "# caracters to be put in one cell ", 1)
If nr Mod 10 <> 0 Or nr < 20 Then MsgBox "only numbers ending with 0 and > 10 ", 48, "20,30,..."
Loop While nr Mod 10 <> 0 Or nr < 20
cell.EntireColumn.ColumnWidth = 120
cell.RowHeight = 350
cell.WrapText = True
cell.Font.Size = 4 'change this to test
For x = 1 To (nr - 20) / 10
temp = temp & "oooooooooo"
Next x
temp = "STARTooooo" & Left(temp, Len(temp)) & "oooooooEND"
cell = temp
If nr < 2000 Then
For x = 10 To nr Step 10
With cell.Characters(Start:=x, Length:=1).Font
.Size = 10
.ColorIndex = 8
End With
Next x
End If
For x = 100 To nr Step 100
With cell.Characters(Start:=x, Length:=1).Font
.Size = 11
.ColorIndex = 4
End With
Next x
For x = 1000 To nr Step 1000
With cell.Characters(Start:=x, Length:=1).Font
.Size = 12
.ColorIndex = 3
End With
Next x
cell.Offset(1, 0).FormulaR1C1 = "=LEFT(R[-1]C,10) & "" "" & RIGHT(R[-1]C,10)"
cell.Select
With ActiveWindow
.DisplayHeadings = False
.Zoom = True
cell.Offset(1, 0).Select
.ScrollRow = 1
.ScrollColumn = 1
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "number of caracters put in the cell: " & " " & Len(temp) & Chr(10) & _
"number of caracters counted in the cell: " & " " & Len(cell) & Chr(10) & _
"START & END at the beginning and at the end to test if everything resides in memory" & Chr(10) & _
"in the cell below please find the first 10 & last 10 caracters found in cell A1", 64, "caracters"
End Sub
Erik