Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Excel

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-18 : 19:53:01

Please, people,
how to (e.g.) multiply by (say) 2 each cell value in some sheet range?
OK, select this range... but what then? Would like to do it with one
single step...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-18 : 20:38:37

Enter the formula you want into one cell, preferably at the top of the range you want to copy it into. After you've entered it, move the cursor over that cell. You'll notice a black outline with a little black square in the lower right corner. Hover over that square, then click and drag the corner over the range of cells. It will copy the formula into those cells.

There are a number of other ways to copy the formula into other cells, but that's the easiest. The Excel help file will tell you more.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-19 : 06:50:49

Thanks, Rob, for your infinite patience, but LOL I'm pretty aware
of this way. The point is that I don't want any formulas in those
cells, I just want to change their values only for one time and
without any manipulations with some staging range.
OK, seems it is the only way of doing the subject.

mr_mist
Grunnio

1870 Posts

Posted - 2003-11-19 : 07:08:46

Make a new sheet.
Find the cell that corresponds to the cell on the other sheet that is the top left of your range.
Type + into the cell, then click onto the old sheet and click the cell that is the top left of your range,
type *2
Press enter

You'll now have one cell on your new sheet that is the old cell * 2.

Now drag the box out with the black plus to the right and then downwards to get all the other values.

Tada.

You now have a copy of your original sheet, with all the values in the first sheet timesd by two.

I don't think that there's a way of just highlighting a batch of squares and seeing them multiplied by 2.

-------
Moo. :)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-19 : 07:23:03

LOL Misty,

I feel myself a bit dizzy :)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-19 : 07:50:49

I don't miss those excel macros


Sub Macro1()

    'Will multiply the value in column "A" by 2 and
    'store the resulting value in B
    
    'A  B
    '1  2
    '2  4
    '3  6
    '4  8
    '5  10
    '6  12
    
    Dim a, b, c As Integer
    
    a = 1 'start cell
    b = 6  'end cell
    c = 0
    
    While a <= b
    
        c = Range("A" & a).Value
        c = c * 2
        Range("B" & a).Select
        Range("B" & a).Value = c
        a = a + 1
    
    Wend

End Sub

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-19 : 07:54:39

There is a macro language in excel. Using that language, you could multiply every selected cell by 2.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-19 : 08:34:40

Double LOL!!

Yesterday I was in terrible hurry at my work,

it was very urgent Excel thing... now they say me:

use macros!!

PS Do you think I am a god in the excel object model??

:)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-22 : 16:03:22

wow.. I found exactly what I was looking for:

1. write into some empty cell number 2
2. copy this cell
3. select req range
4. right click >> special paste >> MULTIPLY operation >> OK

And each cell in the range got multiplied by 2 !!
Perfect. I couldn't believe that there is no trick like this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources