Excel macro to cut cell values in half. 1

[Ironicster]

I am trying to create a macro that will divide by 2 the value of all the selected cells but not sure that it is possible...

Tried also at least come up with a macro that will divide by 2 each individual selected cell but i can´t edit the cell, just write in it, so i can´t get the original value...

Can anyone help me? tks...

 

[Fenrirshowl]

Hi there

In answer to your first question, try something along the lines of:

Sub div2()
For Each cll In Selection
    cll.Value = cll.Value / 2
Next cll
End Sub

I didn't really understand the difference between the first and second parts of your post. It's early though and I might need more coffee.

BTW, macro questions should really be asked in the VBA forum, 707, (as someone searching for help on a similar problem would search there and obviously not find your earlier post and any replies).

Fen

 

[PeterMoran]

Hi,

Do the cells selected contain values or formulas, because if they contain formulas then they will be lost.

It may be appropriate to check for this or change the cell by adding "/2" and possibly an = sign if needed for values. For formulas it may be more difficult if the original formula is to be left and only modified.

I hope it is values that are being divided!

Good Luck!

Peter Moran

 

[Fenrirshowl]

For formulae, try:

Sub div2()
For Each cll In Selection
    cll.Formula = "=(" & Right(cll.Formula, Len(cll.Formula) - 1) & ")/2"
Next cll
End Sub

 

[Ironicster]

Sorry for not posting in correct section, but i am not familiar with this forum.

What i meant in the beggining was that i wasn´t sure if it was possible to divide severall cells by 2 at once or if it had to be done one by one.

tks for the quick help.

 

[AnotherHiggins]

FYI: there is a non-VBA solution to this.

- In an empty cell, type in [!]2[/!]
- Copy that cell
- Select the range you want divided
- Right Click, select Paste Special then Divide
- Click OK

Done!!

Once you know how to do something, you can always generate a macro by turning on your macro recorder (Tools > Macro > Record New Marco) and going through the steps. Then you can always post in forum707 for help cleaning up / improving your code.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Fenrirshowl]

John

That's great - even solves the function problem, which I thought it may have problems with. Can't say I've ever used PasteSpecial for that before, but will keep it in mind for the future.

Advice worthy of a star in my book.

Fen