Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test condition never satisfied in loop
What is wrong with the loop condition in this code?
The purpose of the code is to remove all the spaces before the first character in a column of selected worksheet cells like this: F 1 R H T U N 25.05 8.80 35.38 27.50 24.25 26.00 27.50 22.88 where each row is a value in a cell of a column in the worksheet. When I run the code on these cell values, the test condition, testStr = Left(nxtChar, 1), is never satisified. Thank you. John Wirt Public Sub RemoveSpace() Dim nxtChar As String, testStr As String Dim rngTxt As Range Dim rw As Range testStr = Left(" ", 1) Set rngTxt = Selection For Each rw In rngTxt.Rows nxtChar = rw.Cells(1, 1).Value Do nxtChar = Mid(nxtChar, 2, Len(nxtChar) - 1) Loop While testStr = Left(nxtChar, 1) rw.Value = nxtChar Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test condition never satisfied in loop
What are the "160s" in the original text strings?
Hi. Glad it's working for you. :) I believe the 160's are Nonbreaking spaces ( ). Well, I was just now debating on whether to remove the Clean function. It's been a while, so I just decided to read the Help on Clean in Excel 2007. Excel 2007 has a little more to say on the subject. It actually now mentions the '160' problem. However, it also has this to say. ....The CLEAN function was designed to remove the first 32 non-printing characters in the 7 bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters. So, it looks like we should modify the code now to remove these additional characters also. I did a quick search on these additional characters, but didn't find any documentation. It's never been a problem before, but since Microsoft actually mentions it in help, perhaps the following... Sub FixRange() '// See Help on worksheet function "Clean" Dim Rng As Range Dim Cell As Range Dim s As String Set Rng = Selection With WorksheetFunction For Each Cell In Rng.Cells s = .Clean(Cell.Value) s = Replace(s, Chr(160), vbNullString) 'NonBreaking Space s = Replace(s, Chr(127), vbNullString) '? s = Replace(s, Chr(129), vbNullString) '? s = Replace(s, Chr(141), vbNullString) '? s = Replace(s, Chr(143), vbNullString) '? s = Replace(s, Chr(144), vbNullString) '? s = Replace(s, Chr(157), vbNullString) '? Cell = Trim(s) Next Cell End With End Sub -- HTH :) Dana DeLouis Windows XP & Excel 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif and sum if if two condition are satisfied | Excel Worksheet Functions | |||
Looping till condition is satisfied? | Excel Worksheet Functions | |||
Deleting columns if condition is satisfied | Excel Programming | |||
Copy sheets only if condition is satisfied (mat) | Excel Programming | |||
How to change the value of a cell when a condition is satisfied, but not otherwise? | Excel Discussion (Misc queries) |