Help with Count Function
 

I currently have a cell with formula
=IF(E10="","","Characters")

So it returns the number of characters input in cell -

=IF(B11="","",IF(B11255,"PLEASE GO BACK TO E10 and
PUT IN AN ALT ENTER",""))

What I want is either the amount of characters between the Alt Enters
or if the alt enters are inserted and the text is less than 255 between
those returns that the message doesn't appear.

is that possibel?

:confused:


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393533

Help with Count Function
 

I have some code that seems to work OK for me. It will show a messagebox.
In the Worksheet code module put this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.Intersect(Range("E10"), Target) Is Nothing Then
CountCharacters
End If
End Sub

In a general code module put this procedu

Sub CountCharacters()
Dim SearchString, SearchChar, CurrentPos
Dim LastPos, SearchPos, LineLength, Counter
SearchString = Range("E10").Value ' String to search in.
SearchChar = Chr(10) ' String to search for
SearchPos = 1
LastPos = 0
Counter = 1
CurrentPos = InStr(SearchPos, SearchString, SearchChar, 1)
If CurrentPos = 0 And Len(SearchString) 255 Then GoTo 1
Do Until CurrentPos = 0
CurrentPos = InStr(SearchPos, SearchString, SearchChar, 1)
LineLength = CurrentPos - LastPos
If LineLength 255 Then
1:
Range("E10").Select
MsgBox ("Please Go Back to E10 and put an ALT/ENTER in Line " & Counter)
Exit Sub
End If
SearchPos = CurrentPos + 1
LastPos = CurrentPos
Counter = Counter + 1
Loop
End Sub


Mike F


"Mikeice" wrote in
message ...

I currently have a cell with formula
=IF(E10="","","Characters")

So it returns the number of characters input in cell -

=IF(B11="","",IF(B11255,"PLEASE GO BACK TO E10 and
PUT IN AN ALT ENTER",""))

What I want is either the amount of characters between the Alt Enters
or if the alt enters are inserted and the text is less than 255 between
those returns that the message doesn't appear.

is that possibel?

:confused:


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393533

Help with Count Function
 

Thanks so much Mike but is it possible that after the first messag
appears can it then do the count again if the cell E10 get more tex
again typed into it

--
Mikeic
-----------------------------------------------------------------------
Mikeice's Profile: http://www.excelforum.com/member.php...fo&userid=2246
View this thread: http://www.excelforum.com/showthread.php?threadid=39353

Help with Count Function
 

Yes, it does that automatically every time the cell value is changed
with the WorkSheet Event code in Private Sub Worksheet_SelectionChange(ByVal
Target As Range). You must understand that this does not do real time
checking of the text as you are typing. I am not sure that can be done. But,
as soon as you hit Enter or click to another cell, E10 will be re-evaluated
by the code. The messagebox attempts to give you a line number where the
Alt/Enter is needed. Of course the number of lines you see displayed depends
entirely upon how wide column E is. What the Line number refers to is which
carriage return has more than 255 characters before it. So it is really
counting carriage returns, which is what you requested. The Line number is
something I put in extra to help you know which carriage return has too many
characters before it.
If you type 150 characters and Alt/Enter, then type 350 characters and
Enter, here is what the code determines:
Starting at character 1 it finds the position of the next Carriage return at
151. Determines the length is OK and loops through the text again starting
at character 152. It does not find a 2nd carriage return. It then subtracts
the position of the last carriage return (151) from the entire number of
characters in E10 (500) and if that is greater than 255, which it is (349),
then the message will direct you to Line 2.

By the way, I found a flaw in my code that goes in the general module, so
here is the updated procedu

Sub CountCharacters()
Dim SearchString, SearchChar, CurrentPos
Dim LastPos, SearchPos, LineLength, Counter
SearchString = Range("E10").Value ' String to search in.
SearchChar = Chr(10) ' String to search for
SearchPos = 1
LastPos = 0
Counter = 1
CurrentPos = InStr(SearchPos, SearchString, SearchChar, 1)
If CurrentPos = 0 And Len(SearchString) 255 Then GoTo 1
Do Until CurrentPos = 0
CurrentPos = InStr(SearchPos, SearchString, SearchChar, 1)
LineLength = CurrentPos - LastPos
If CurrentPos = 0 And Len(SearchString) - LastPos 255 Then GoTo 1
If LineLength 255 Then
1:
Range("E10").Select
MsgBox ("Please Go Back to E10 and put an ALT/ENTER in Line " & Counter)
Exit Sub
End If
SearchPos = CurrentPos + 1
LastPos = CurrentPos
Counter = Counter + 1
Loop
End Sub

Mike F

"Mikeice" wrote in
message ...

Thanks so much Mike but is it possible that after the first message
appears can it then do the count again if the cell E10 get more text
again typed into it?


--
Mikeice
------------------------------------------------------------------------
Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
View this thread: http://www.excelforum.com/showthread...hreadid=393533