Creating a macro to replace numbers

P

Phil Tukey

I am having problems with macros. The problem is that I have a co-worker
that wants to replace certain numbers with other numbers, but here the
kicker. The numbers don't exist. For example.... he wants to replace 13.00
(which is really 13) with 13.5. but he wants to replace all the numbers (ie
24.00 or 15.00, which is just 24 and 15) with .5 after them. I have been
trying to create a macro for this but have failed. Is there anyone out there
who has dealt with this and who might be able to help me?
 
R

roer.garcia

if you coworker only wants to add .5 to any value on the cells, u can
do this:

in a cell put a .5
copy the value
hilite the range to modify
paste special add OK

the value is added to each cell
 
B

Bernard Liengme

Firstly, there is no difference between 13 and 13.00; the same number is
stored only the display differs.
To add 0.5 to a selection of numbers:
1) Enter 0.5 is a free cell and Copy it
2) Select the cell to be changed; is they are non-contiguous hold CTRL while
moving the mouse around
3) Use menu command Edit | Paste Special -> Add
4) Clear the cell with .5
best wishes
 
P

Phil Tukey

Thats an Idea. But he wantes to replace all the numbers at the same time.
You can't do it with the find and replace option, because Excel doesn't see
..00 as part of actual number. It sees it as a display. So I hope that helps
clearify the problem.
 
P

Phil Tukey

Soory one more thing, it sounds like you are saying that you can replace the
13 with .5 or are you saying that you can replace 13 with 13.5 by doing what
you have suggested?
 
R

roer.garcia

is the cell format TEXT ?

"13.00" and 13.00 is not the same value.

show us the macro thing plaz. may be...
 
B

Bob Phillips

That is a one-step operation, it is not find and replace. See Bernard's
answer, it might be a tad clearer, although it is the same method.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
P

Phil Tukey

Well that worked and I will show my co-worker and see what he thinks thanks
for all your help.
 
P

Phil Tukey

Thank you that worked. Your a genious.
Bernard Liengme said:
Firstly, there is no difference between 13 and 13.00; the same number is
stored only the display differs.
To add 0.5 to a selection of numbers:
1) Enter 0.5 is a free cell and Copy it
2) Select the cell to be changed; is they are non-contiguous hold CTRL
while moving the mouse around
3) Use menu command Edit | Paste Special -> Add
4) Clear the cell with .5
best wishes
Click to expand...
 
Top