PDA

View Full Version : Need help with vba code for deleting trailing spaces before or after an entry in a co



lucpian
03-26-2008, 11:31 AM
Hi All,

I do have a vba code that I wrote that validate length of entries in a column, and is working, but does not check if there is a trailing space before or after the entry. Please, I would be grateful if anyone in this forum will help me out.

Thanks

Lucpian

MikeO
03-26-2008, 11:44 AM
It sounds like you just need to use the Trim function.
NewEntryString = Trim(OldEntryString)

lucpian
03-26-2008, 11:52 AM
Here is the code

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' checklength
' This is part of the fields Validation calling function
' which check for length of entries in cells in the Worksheet, and colors it
' yellow.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function checklength(columnname As Integer, length As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Sheet1.Cells(R, columnname).Value
If strVal <> "" And (Not (Len(strVal) = length)) Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
End If
Next

End Function

My question is where do I place this trim code to indicate that I do not want a trailing space before or after?

Thanks

Lucpian:(

mdmackillop
03-26-2008, 11:59 AM
Hi Lucpian,
When you post code, please select it and click the VBA button to format it as shown.

MikeO
03-26-2008, 12:00 PM
Try this:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' checklength
' This is part of the fields Validation calling function
' which check for length of entries in cells in the Worksheet, and colors it
' yellow.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function checklength(columnname As Integer, length As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Trim(Sheet1.Cells(R, columnname).Value)
If strVal <> "" And (Not (Len(strVal) = length)) Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
End If
Next
End Function


I'm slightly unclear about what you really want, so this is just a guess.

Bob Phillips
03-26-2008, 12:19 PM
Function checklength(columnname As Integer, length As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Sheet1.Cells(R, columnname).Value
If Trim(strVal) <> "" And Len(Trim(strVal)) <> length Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
End If
Next
End Function

mdmackillop
03-26-2008, 12:21 PM
Note that Trim will not handle non-printing characters such as Chr(160) which can occur in imported data.