addspace macro

[David Ellis - Indiana State Fair Comm.]

I created the following macro for a workbook:

Sub addspace()
Dim cell As Object
For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next
End Sub

It worked perfectly the first time I used it. I then attempted to self
certify the macro and in doing so changed some of my security settings. Now
the exact same macro won't run. I get no error... it just doesn't run. I
think I've reset everything back to the way it was, even deleted my self
certification, but I can't get it to work. Any thoughts?

 

[Mike H]

David,

Why do you think it isn't working. All the macro does (and you must have a
reason) is put a space in front of each cell in a selection and then
immediatly take that space away. It will work so fast you won't be able to
see it work.

One way to see if it is working is to put a STOP in the code and then run it

Sub addspace()
Dim cell As Object
For Each cell In Selection
cell.Value = " " & cell.Value
Stop
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next
End Sub

Mike

 

[David Ellis - Indiana State Fair Comm.]

The column I have selected has both text and numbers. The text has the small
green triangle in the corner indicating a warning to this effect. When I ran
the macro the first time those warning indicators went away. Also, the
workbook is linked to an Access database. When I tried to append data from
the linked file I would get a "Numeric field overflow" error. I tracked that
error back to the text/number mix issue, created the macro and ran it, and
that fixed the error. Now when I run the macro I still get the error.

 

[Patrick Molloy]

as an alternative, you could format the colum as text

dim cell as range
range("D").NumberFormat = "@"
for each cell in range("D").Cells
cell.value = cell.value
next
range("D").NumberFormat = "General"

 

[David Ellis - Indiana State Fair Comm.]

When I try to create the Macro you have suggested I get a "Wrong number of
arguments or invalid property assignment" error.

I don't really know VB. I copy the original Macro from MicroSoft.

I have tried to simply highlight the range in question and format it as Text
using the Format menue. That didn't solve my problem