How can I insert a space before the last character in a range of cells

[Jeff]

Any help greatly appreciated!

Jeff
MCP 70-215

 

[Tom Ogilvy]

Sub Addspace()
Dim cell as Range
for each cell in selection
if not cell.hasformula then _
cell.value = left(cell.value,len(cell.value)-1) & " " & Right(Cell.Value,1)
Next
End Sub

 

[David McRitchie]

Hi Jeff,
Assume these are for text and not numbers.
SpecialCells is an automatic limitation to the used range,
in addition limited to text constants in the following.
(untested code)

Sub Jeff_space()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell as Range
On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
if length(cell.value) > 0 then
cell.value = left(cell.value,length(cell.value)) & " " & right(cell.value)
end if
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

More information on the extra coding in
http://www.mvps.org/dmcritchie/excel/proper.htm

 

[David McRitchie]

Correction: Should have tested, my code line should have been more like Tom's,
rest of macro improves performance.

cell.value = left(cell.value,len(cell.value)-1) & " " & right(cell.value)

 

[Jeff]

Wow, that was a fast response, thanks very much. That
works lovely except on completion it throws up an error
and starts debug with ref to the last line of code. run
time error 5 invalid procedure call or argument.

thid is my code -

Private Sub CommandButton1_Click()
Range("e2:e2000").Select
Application.CutCopyMode = False
Selection.Copy
Range("f2:f2000").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F2:F2000").Select
Selection.Replace What:=" ", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then _
cell.Value = Left(cell.Value, Len(cell.Value) - 1) & " " &
Right(cell.Value, 1)
Next
End Sub

 

[Tom Ogilvy]

Looks like a problem with word wrap in the email:

cell.Value = Left(cell.Value, Len(cell.Value) - 1) & " " &
Right(cell.Value, 1)

should all be one line of code or you can put in a line continuation
charater (space and underscore)

cell.Value = Left(cell.Value, Len(cell.Value) - 1) & _
" " & Right(cell.Value, 1)

 

[jeff]

sorry thats not it, because The line was only wrapped i
the reply i gave. Maybe i could figure it out if i
understood the code you gave me (it would be great if you
could you comment it). when does the next end?. Ideally i
would like this to operate on a variable number of rows in
one column. At the moment i have set a range 2-2000 until
i figure out how to do it as a variable range. Maybe this
setting of the range is in conflict with the code i added
from you.

Thanks

 

[Guest]

Thanks for your reply would you mind looking at the my
replys on Tom's thread

 

[Tom Ogilvy]

I copied this out of your email and made it a sub:

Sub Addspace()
Dim cell As Range

For Each cell In Selection
If Not cell.HasFormula Then _
cell.Value = Left(cell.Value, Len(cell.Value) - 1) _
& " " & Right(cell.Value, 1)
Next
End Sub

It ran fine for me.

I added a check so it only tries to put a space in if there are two
characters in the cell.

Sub Addspace()
Dim cell As Range

' loop over the cells in the selection
For Each cell In Selection
' if the cell has a formula, skip it
If Not cell.HasFormula Then
if len(cell.Value) > 1 then
' take the left characters for the len of the string minus 1,
' concatenate a space to that, take the right single character and
' concatenate it to the end of the space
cell.Value = Left(cell.Value, Len(cell.Value) - 1) _
& " " & Right(cell.Value, 1)
End if
Next
End Sub