all asterisk which has placed unevenly in the same column of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

Dim cell as Range
for each cell in selection
if not cell.HasFormula then
cell.formula = cell.Value
end if
Next

--
Regards,
Tom Ogilvy

"ªü¤T" wrote in message
...
all asterisk which has placed unevenly in the same column of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

Edit | Replace...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
all asterisk which has placed unevenly in the same column of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

tried but failed....anyway, thank you.

"Tushar Mehta" ¼¶¼g©ó¶l¥ó·s
»D:MPG.1af8295d9fe2b34098978e@news-server...
Edit | Replace...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,

says...
all asterisk which has placed unevenly in the same column of cells
by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

Hi
try something like
sub foo()
with activesheet.range("A1:A100")
.numberformat="0"
.value=.value
end with
end sub
-----Original Message-----
all asterisk which has placed unevenly in the same column
of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

.

Sorry that I just want to remove the [ ' ] in front of cells with
numbers, say to change [ '1234 ] to become [ 1234 ].

Anyway, many thanks.

"Tom Ogilvy" ¼¶¼g©ó¶l¥ó·s»D
...
Dim cell as Range
for each cell in selection
if not cell.HasFormula then
cell.formula = cell.Value
end if
Next

--
Regards,
Tom Ogilvy

"ªü¤T" wrote in message
...
all asterisk which has placed unevenly in the same column of cells
by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

Great, its work! Many thanks.

"Frank Kabel" ¼¶¼g©ó¶l¥ó·s»D
...
Hi
try something like
sub foo()
with activesheet.range("A1:A100")
.numberformat="0"
.value=.value
end with
end sub
-----Original Message-----
all asterisk which has placed unevenly in the same column
of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

.

The functionality of Frank's code is identical to mine - yet you imply that
mine does not work and his does.

Did you bother to try mine? Why the negative response?

Is the difference that you don't know how to add the sub declaration at the
start and the End Sub statement on the End.

Just interested in understanding what prevented you from using the solution
provided - your own ineptitude or was there some technical problem?

--
Regards,
Tom Ogilvy





"ªü¤T" wrote in message
...
Great, its work! Many thanks.

"Frank Kabel" ¼¶¼g©ó¶l¥ó·s»D
...
Hi
try something like
sub foo()
with activesheet.range("A1:A100")
.numberformat="0"
.value=.value
end with
end sub
-----Original Message-----
all asterisk which has placed unevenly in the same column
of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

.

I have tried both but I think your macro is trying to turn formulas to
values.
My problem is just to remove the ' in front of numbers only.

Really thanks to you and I will keep this macro program for my future
problems.

Regards

"Tom Ogilvy" ¼¶¼g©ó¶l¥ó·s»D
...
The functionality of Frank's code is identical to mine - yet you
imply that
mine does not work and his does.

Did you bother to try mine? Why the negative response?

Is the difference that you don't know how to add the sub declaration
at the
start and the End Sub statement on the End.

Just interested in understanding what prevented you from using the
solution
provided - your own ineptitude or was there some technical problem?

--
Regards,
Tom Ogilvy





"ªü¤T" wrote in message
...
Great, its work! Many thanks.

"Frank Kabel" ¼¶¼g©ó¶l¥ó·s»D
...
Hi
try something like
sub foo()
with activesheet.range("A1:A100")
.numberformat="0"
.value=.value
end with
end sub
-----Original Message-----
all asterisk which has placed unevenly in the same column
of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

.

I had a similar problem i.e. numbers with prefix ' causing the column to be left aligned

cell.HasFormula for such cells returns FALSE. Therefore, although cell.Value does remove the ', it is never executed.
Did I miss something? I am using Win2000 & Excel 2000

Sorry that I have misled you since wrongly use the word asterisk [ * ]
for a quotation mark [ ' ].

Regards
Ah San

"Tom Ogilvy" ¼¶¼g©ó¶l¥ó·s»D
...
The functionality of Frank's code is identical to mine - yet you
imply that
mine does not work and his does.

Did you bother to try mine? Why the negative response?

Is the difference that you don't know how to add the sub declaration
at the
start and the End Sub statement on the End.

Just interested in understanding what prevented you from using the
solution
provided - your own ineptitude or was there some technical problem?

--
Regards,
Tom Ogilvy





"ªü¤T" wrote in message
...
Great, its work! Many thanks.

"Frank Kabel" ¼¶¼g©ó¶l¥ó·s»D
...
Hi
try something like
sub foo()
with activesheet.range("A1:A100")
.numberformat="0"
.value=.value
end with
end sub
-----Original Message-----
all asterisk which has placed unevenly in the same column
of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

.

The code works exactly as designed.

Not False = True, and the cell.Formula = Cell.Value is executed whenever the
cell does not have a formula - exactly as designed.

Sub Tester9()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then
cell.Formula = cell.Value
End If
Next

End Sub

Maybe you tested with your own version and neglected the NOT statement.

Did I miss something? I am using Win2000 & Excel 2000
Apparently

--
Regards,
Tom Ogilvy

"AA2e72E" wrote in message
...
I had a similar problem i.e. numbers with prefix ' causing the column to
be left aligned.

cell.HasFormula for such cells returns FALSE. Therefore, although
cell.Value does remove the ', it is never executed.
Did I miss something? I am using Win2000 & Excel 2000

I think your macro is trying to turn formulas to
values.

I understand. It is your ineptitute. It is exactly the opposite. It keeps
you from screwing up cells with formulas, unlike Frank's code which offers
no such protection. (no criticism of Frank intended - no one is paying for
turn key solutions).

Thanks for explaining.

Regards,
Tom Ogilvy

"ªü¤T" wrote in message
...
I have tried both but I think your macro is trying to turn formulas to
values.
My problem is just to remove the ' in front of numbers only.

Really thanks to you and I will keep this macro program for my future
problems.

Regards

"Tom Ogilvy" ¼¶¼g©ó¶l¥ó·s»D
...
The functionality of Frank's code is identical to mine - yet you
imply that
mine does not work and his does.

Did you bother to try mine? Why the negative response?

Is the difference that you don't know how to add the sub declaration
at the
start and the End Sub statement on the End.

Just interested in understanding what prevented you from using the
solution
provided - your own ineptitude or was there some technical problem?

--
Regards,
Tom Ogilvy





"ªü¤T" wrote in message
...
Great, its work! Many thanks.

"Frank Kabel" ¼¶¼g©ó¶l¥ó·s»D
...
Hi
try something like
sub foo()
with activesheet.range("A1:A100")
.numberformat="0"
.value=.value
end with
end sub
-----Original Message-----
all asterisk which has placed unevenly in the same column
of cells by
formulas?

e.g. [ '12345678 ] change to [ 12345678 ]

.