Is there any method to remove...

[=?big5?B?qvykVA==?=]

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

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

 

[Tom Ogilvy]

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

 

[Tushar Mehta]

Edit | Replace...

--
Regards,

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

 

[=?big5?B?qvykVA==?=]

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

Edit | Replace...

--
Regards,

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

says...

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

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

 

[Frank Kabel]

Hi
try something like
sub foo()
with activesheet.range("A1:A100")
.numberformat="0"
.value=.value
end with
end sub

 

[=?big5?B?qvykVA==?=]

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

Anyway, many thanks.

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

--
Regards,
Tom Ogilvy

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

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

 

[=?big5?B?qvykVA==?=]

Great, its work! Many thanks.

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 ]

.

 

[Tom Ogilvy]

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

Great, its work! Many thanks.

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 ]

.

 

[=?big5?B?qvykVA==?=]

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

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

Great, its work! Many thanks.

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 ]

.

 

[Guest]

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

 

[=?big5?B?qvykVA==?=]

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

Regards
Ah San

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

Great, its work! Many thanks.

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 ]

.

 

[Tom Ogilvy]

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

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.

 

[Tom Ogilvy]

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

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

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

Great, its work! Many thanks.

"Frank Kabel" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D
:[email protected]...
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 ]

.