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


.



.