TRIM problem

  • Thread starter Thread starter Dan E
  • Start date Start date
D

Dan E

Can anyone see anything wrong with this short sub - it seems to go into an
infinite loop:-

Sub Trim_Text()
Dim cell As Range
On Error GoTo ws_next5
For Each cell In ActiveSheet.UsedRange
ActiveCell.Value =
Application.WorksheetFunction.Trim(ActiveCell.Value)
ws_next5:
Next
End Sub

All suggestions/help gratefully received and acknowledged!

TIA

Dan
 
Your code would only change the active cell, not each cell in the used
range, and the Next keeps sending it back into the loop. You could use
the following revision:

Sub Trim_Text()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
cell.Value = Application.WorksheetFunction.Trim(cell.Value)
Next cell
End Sub
 
Ah! Many thanks, Debra.

Dan
Debra Dalgleish said:
Your code would only change the active cell, not each cell in the used
range, and the Next keeps sending it back into the loop. You could use the
following revision:

Sub Trim_Text()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
cell.Value = Application.WorksheetFunction.Trim(cell.Value)
Next cell
End Sub
Click to expand...
 
And just in case you had formulas you didn't want to lose you could add in a
condition that tested to see if there was a formula in the cell, eg
something like:-

Sub Trim_Text()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
With cell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Trim(cell.Value)
End If
End With
Next cell
End Sub
 
Thanks, Ken. I did think about the effect of TRIM on formulae but thought
I'd be OK 'cos I didn't see any critical spaces in mine. Are there common
formula usages where trimming spaces would make a difference? I'm guessing
only in a text string?

TIA,

Thanks again,

Dan
 
LOL - Try it without -You will lose any formula on the sheet, regardless of
what type of formula it is.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

Dan E said:
Thanks, Ken. I did think about the effect of TRIM on formulae but thought
I'd be OK 'cos I didn't see any critical spaces in mine. Are there common
formula usages where trimming spaces would make a difference? I'm guessing
only in a text string?

TIA,
Click to expand...
<snip>
 
Dan

In addition to hammering on the ActiveCell, as has been discussed in this
thread, and replacing formulas with their result, if you get an error,
you'll go to the next cell, but then if you got another, you'll get a halt,
because you've not executed a Resume or Resume Next statement. You'll have
an error within an error.
 
I must be thick, because I can't see why that would happen - my formulae
have no spaces at all, actually. This is a simple physicist here, ya know!
Maybe words of half a syllable?.... :-) In other words, please to explain,
kind sir.

TIA

Dan
 
This line:

..Value = Application.WorksheetFunction.Trim(cell.Value)

doesn't care if the cell contained a formula when you started. When you're
done, the value is the trimmed value of what that cell evaluated to.

It's a lot like copy|paste special|values.
 
Earl, Ken - I really appreciate all the help you guys give - just so you
know. One of these days I may get to a stage where I have an inkling of the
magnitude of what I don't know... Here's what the sub looks like now -
appears to run OK, but takes a little less than a minute (about) to run on a
54 column 197 row sheet, so a little slow - and it doesn't mess with my
formulas (thanks for getting to me in time to prevent a nasty scene of me
sobbing on my keyboard). So the line .Value =
Application.WorksheetFunction.Trim(cell.Value) replaces a formula with its
value, is that correct?
______________________
Sub Trim_Text()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
With cell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Trim(cell.Value)
End If
End With
Next cell
End Sub
____________

TIA,

Dan
Earl Kiosterud said:
Dan

In addition to hammering on the ActiveCell, as has been discussed in this
thread, and replacing formulas with their result, if you get an error,
you'll go to the next cell, but then if you got another, you'll get a
halt, because you've not executed a Resume or Resume Next statement.
You'll have an error within an error.
Click to expand...
 
LOL - Personally I wouldn't use it at all.

You initially asked for clarification on a point of syntax, and that is what
you got. You never asked if we would do it that way. :-)

Seriously though, that routine will look at every single cell, regardless of
whether there is anything in it, makes a call as to whether there is a
formula in it, then apply a worksheet function to it, and then move on to
the next cell, and and this all takes time. A lot of it is wasted time
because there may well be nothing in some of those cells, but it still goes
through the motions.

Far better to use the built-in 'SpecialCells' capability that allows you to
go straight to only the cells that contain perhaps constants or formulas, or
whatever it is you choose. To that end I simply use Dave McRitchies Trimall
Macro which cleans up all kinds of garbage from your data, especially if it
is imported data from HTML. For the code try here:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

This piece of code gets used almost daily on my machine.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

Dan E said:
Earl, Ken - I really appreciate all the help you guys give - just so you
know. One of these days I may get to a stage where I have an inkling of the
magnitude of what I don't know... Here's what the sub looks like now -
appears to run OK, but takes a little less than a minute (about) to run on a
54 column 197 row sheet, so a little slow - and it doesn't mess with my
formulas (thanks for getting to me in time to prevent a nasty scene of me
sobbing on my keyboard). So the line .Value =
Application.WorksheetFunction.Trim(cell.Value) replaces a formula with its
value, is that correct?
______________________
Sub Trim_Text()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
With cell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Trim(cell.Value)
End If
End With
Next cell
End Sub
____________

TIA,

Dan
Click to expand...
 
What can I say, but thank you - Ken and Dave and Earl and ......

I learn. Therefore I am. Or vice versa.

Dan
 
So the line .Value =
Application.WorksheetFunction.Trim(cell.Value) replaces a formula with its
value, is that correct?
Click to expand...

Yes, and this is pretty important at times. Take this code:

Sub test()
ActiveCell = ActiveCell
End Sub

Looks like pretty harmless nonsense, but it is a formula destroyer that has
caused damage lots of places. A cell has several properties, like font name,
border color, ... and the more important Formula (what we see in the formula
bar) and Value (what we see in the cell itself). When we don't specify a
property, then VBA uses the default. For cells this is the Value property,
so the code really says

ActiveCell.Value = ActiveCell.Value

A cell can have a Formula =A1*4, and if A1 is 3 then the cell Value is 12.
But if we enter a value into a cell then the formula goes; the code is like
entering 12 into the cell.

HTH. Best wishes Harald
 
Many thanks, Harald - yes it does help.

Dan
Harald Staff said:
Yes, and this is pretty important at times. Take this code:

Sub test()
ActiveCell = ActiveCell
End Sub

Looks like pretty harmless nonsense, but it is a formula destroyer that
has
caused damage lots of places. A cell has several properties, like font
name,
border color, ... and the more important Formula (what we see in the
formula
bar) and Value (what we see in the cell itself). When we don't specify a
property, then VBA uses the default. For cells this is the Value property,
so the code really says

ActiveCell.Value = ActiveCell.Value

A cell can have a Formula =A1*4, and if A1 is 3 then the cell Value is 12.
But if we enter a value into a cell then the formula goes; the code is
like
entering 12 into the cell.

HTH. Best wishes Harald
Click to expand...
 
Thanks for the macro pointer, Ken. Speaking of Poirot et al, as we were
:-) - have you caught Jeeves and Wooster at all? Not murder-most-foul
stuff, but very well done. And then there's Wire in the Blood, and Waking
the Dead - both really good; but definitely not Christie! You probably know
of these, but just in case...

These sheets I'm working on - they're a voluntary effort, aimed at helping
out a large care facility that was and is struggling with scheduling, and
with getting acceptable reporting for management on two types of overtime -
normal (>40 hrs /wk) and continuity of care (CC) overtime (again >40 /wk)
where a single 12-hour shift is covered for the whole week by two nurses or
techs. The schedule user is ADON at the facility, and if you have any
inkling of how hard and long such folks work, you like I would cut her a lot
of slack in making the odd mistake in leaving a space where there shouldn't
be one. I'm just aiming to make it as easy to use (including usability and
making it easy to spot shifts that aren't covered) and as foolproof as
possible, while giving management the information they want. Of course,
it's a good learning experience for me, in my spare time (I manage a real
estate business - perfect for a retired physicist - not!)! I can't tell you
how much the help I've received on this list has meant to me, and eventually
to the quality of care for the patients. I know there has to be good
scheduling software out there, and maybe eventually management will see
sense and buy a commercial solution, but at the moment - no, they won't.

Thanks to you and all, once again. By the by - I have a puzzler in working
out/displaying the overtime, whenever anyone feels like doing something a
little clever; I have little idea how to do this. In a continuity of care
shift (each shift has a code, say R1), 2 people cover the whole week, for a
12-hour shift - one does 3 shifts a week, the other 4, and they probably
swap in the second week. This gives typically 8 hours of CC (contiuity of
care) overtime each week. CC overtime is thought of as "good" overtime.
Now - sometimes one of these workers will fill in on another shift (say R2)
when someone is sick. This bumps up their time, and we now have a mix of CC
and non-CC overtime. My current way of handling this sweeps all the
overtime into CC overtime, so CC is overstated, and, if the user fixes it
manually, the fix gets undone next time the macro is run. I can't even
begin to think of how to separate out the two. Here's the process. The
sheet user manually scans the sheet for a CC situation. Sometimes both
weeks are covered as CC, so she puts a 3 in a specific column for both
rows/people. If only week 1 is covered as CC, she enters a 1, and if only
week 2 is covered as CC, she enters a 2. The current macro looks for a 1, 2
or 3 in that column, goes to a different sub for each case, works out the
overtime worked for the CC week(s), and puts it into CC overtime. Ideally,
the macro should also look at the time contributed by different shift codes,
and, if there is a CC situation, allocate the overtime for the non-CC shift
to normal overtime, not CC overtime, while still correctly allocating CC
overtime. Tricky, and beyond me. Any suggestions/assistance gladly
received.

TIA,

Dan
<snip>
 
Back
Top