This is for NateO

[elgringo56]

Nate, a few weeks ago you told me about the Trim function when checking cells. Thanks for that one. You cant imagine how many hours of chaseing my tail you have saved me with that one. Do you know why, or where I can find info as to why cells will act as if they have something in them that is not there? I am curious about it is all. Thanks again

[NateO]

Hello El Gringo, not too certain it was I who mentioned the trim function. But, Excel looks at every charchter space in a string. Sometimes data transfers create visible and hidden code additions to the string.

The trim function removes all non-printable spaces from text except for single spaces between words.

e.g., =22&char(32) does not equal 22 in Excels eyes.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-19 17:52 ]

[Ivan F Moala]

On 2002-05-19 17:50, NateO wrote:
Hello El Gringo, not too certain it was I who mentioned the trim function. But, Excel looks at every charchter space in a string. Sometimes data transfers create visible and hidden code additions to the string.

The trim function removes all non-printable spaces from text except for single spaces between words.

e.g., =22&char(32) does not equal 22 in Excels eyes.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-19 17:52 ]

Hi elgringo, just to add to Nates answer.
You should also consider th CLEAN function
IF you typically download data to worksheets.
This function removes any nonprintable characters from text.
These nonprinting characters are often found in data which has been imported
from other systems such as database imports from mainframes, Html data, Web sites etc.
eg Chr(10),chr(13) etc. You can sometimes see these in the cells as
the shapes = [Squares]

Syntax =CLEAN(Text) Look up online help for this if you don't already know.

I generally run a routine when doing this;

Sub Clean_Trim()

Dim CleanTrimRg As Range

Dim oCell As Range

Dim Func As WorksheetFunction



Set Func = Application.WorksheetFunction



On Error Resume Next

Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)

If Err Then MsgBox "No data to clean and Trim!": Exit Sub



For Each oCell In CleanTrimRg

    oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))

Next





End Sub





_________________

Kind Regards, 

 Ivan F Moala

Have a Nice day 



[ This Message was edited by: Ivan F Moala on 2002-05-19 18:03 ]
		

[elgringo56]

Hi Nate and Ivan. Sorry it has taken so long to get back on this, but I have been trying to research these Non printable characters to find out what they are. I have a situation where they are showing up in some stuff I am getting from a guy in San Diego. Cant find diddley on it. Ivan, thanks for the sub to clean with though, it works really well except I am such a Novice I dont really understand much of it. I have a book being brought down here next week called VBA for dummies that I hope will get me a little more started on this stuff, I find it a lot of fun. I have done a data base with it, but it is probably pretty crude since I have little knowledge of programming. Have been a hardware person all my life, never dabbled in the code. Well, thanks a lot for your help, some day I may be able to answer a few questions, also.

[stevebausch]

Here is another way of doing the clean-up, which may or not be very elegant.

Actually, after looking at the previous submission, I really don't want to post this; looks a bit clumsy.

However, it does illustrate the efforts needed to ensure that a Worksheet_Change event routine doesn't choke on multiple selections, how to loop through a multiple selection (not really elegant, though), and how to check that you aren't trying to clean-up the whole sheet, which could hang the machine.


So, here goes:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Horiz As Long, Vert As Long, HorizLoop As Long, VertLoop As Long

Const Max As Long = 100


Application.EnableEvents = False

If (VarType(Target) And vbArray) = vbArray Then
'dealing with multiple cells
Vert = UBound(Target.Value, 1)
Horiz = UBound(Target.Value, 2)
If Horiz * Vert < Max Then 'Too many cells will lock-up machine
For HorizLoop = 1 To Horiz
For VertLoop = 1 To Vert
Target.Cells(VertLoop, HorizLoop) = _
WorksheetFunction.Clean(Target.Cells(VertLoop, HorizLoop))
Next
Next
Else
MsgBox "You are trying to modify " & Horiz * Vert & " cells at a time", vbOKOnly, "Limit of " & Max & " cells"
End If

Else
Target = WorksheetFunction.Clean(Target.Cells)
End If

Application.EnableEvents = True

End Sub


[ This Message was edited by: stevebausch on 2002-08-17 20:29 ]