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

Any help greatly appreciated!

Jeff
MCP 70-215

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

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
--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Any help greatly appreciated!

Jeff
MCP 70-215

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

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Jeff" wrote in message ...
Any help greatly appreciated!

Jeff
MCP 70-215

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

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)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

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
-----Original Message-----
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
--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Any help greatly appreciated!

Jeff
MCP 70-215


.

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

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)

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
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
-----Original Message-----
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
--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Any help greatly appreciated!

Jeff
MCP 70-215


.

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

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
-----Original Message-----
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)

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
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
-----Original Message-----
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
--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Any help greatly appreciated!

Jeff
MCP 70-215


.



.

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

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

-----Original Message-----
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)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm


.

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

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

--
Regards,
Tom Ogilvy

"jeff" wrote in message
...
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
-----Original Message-----
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)

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
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
-----Original Message-----
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
--
Regards,
Tom Ogilvy

"Jeff" wrote in message
...
Any help greatly appreciated!

Jeff
MCP 70-215


.



.