Excel 2002 - Multiplication

[Sherri Baker]

I would like to take a column of cells, traverse through
them, multiply the number in each cell by a constant
value, and return the result into the same cell.

Any help will be greatly appreciated.

 

[Doug Glancy]

Assuming your data begins in A1 and the constant is 2 this would do what you
want:

Sub test()

Dim r As Range

For Each r In Range("A1", Range("A65536").End(xlUp))
r.Value = r.Value * 2
Next r

End Sub

 

[Sherri]

Doug,

Thank you very much.

Does the statement Range("A65536") mean the last row in
the worksheet? Can I customize that to the ending row I
want it to be?

And, the .End(x1up) statement is confusing to me.
Could you explain why that is needed, please?

Again, thank you.

Sherri

 

[Ken Wright]

Just on the offchance you didn't need a vba solution. Assuming you wanted to mutiply them all by
say 6, then put 6 in a cell, copy it, select the range of data and do Edit / Paste Special /
Mutiply.

 

[Doug Glancy]

Sherri,

Range("A65536").End(xlUp) means the last non-empty cell in the column. It's
saying start at the last row (65536) and go up to the first cell with
contents (the "end"). This just the second part of the larger range
statement Range("A1", Range("A65536").End(xlUp)), which translates to "A1
through the last non-empty cell." If the range you're looking at will
always be the same, you could use something like Range("A1:A20"):

Sub test()

Dim r As Range

For Each r In Range("A1:A20")
r.Value = r.Value * 2
Next r

The first way is more flexible, because it will do all the non-empty cells
in the column, assuming that's what you want.

hth,

Doug

End Sub

 

[Sherri]

Ken,

What an amazingly simple solution. Thanks!

But I think an overall VBA solution would help others
in the future.

Thanks,
Sherri

-----Original Message-----
Just on the offchance you didn't need a vba solution.

Assuming you wanted to mutiply them all by

say 6, then put 6 in a cell, copy it, select the range

of data and do Edit / Paste Special /

 

[Sherri]

Doug,

Thank you for explaining that to me. And, thank you again
for the help.

Sherri

 

[Ken Wright]

OK, then how about the following,

Select the range of cells in question and run this:-

Sub multiply()

Dim m As Long
Dim cell As Range

m = InputBox("What value do you want to multiply by")

Application.ScreenUpdating = False

For Each cell In Selection
If cell.HasFormula = False Then
cell.Value = cell.Value * m
End If
Next cell

Application.ScreenUpdating = True

End Sub

 

[David McRitchie]

Hi Sherri,
Using a macro is going to be faster, but there is one difference
between the macro solution and the use of paste special multiply.

The macros are avoiding formulas, because they would be converting
them to constant values otherwise; whereas, the manual solution
-- Edit / Paste Special / Multiply -- will retain the formula with the multiplier.

In fact before this actually went out noticed a similar question and
comment in another thread, where Tom Ogilvy responded with a similar remark.
http://google.com/groups?threadm=#[email protected]

 

[Ken Wright]

Which also makes it nice and easy to get a cell reference in to all the formulas as well. Doing
as Dave's note suggests, you can multiply by 99999 say (Assuming they are all formulas), and then
do a replace all, replacing 99999 with A1. This will put in the cell A1 into all your formulas
and allow you to make any changes you like quite easily simply by putting in the real value to
multiply with into cell A1.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

Hi Sherri,
Using a macro is going to be faster, but there is one difference
between the macro solution and the use of paste special multiply.

The macros are avoiding formulas, because they would be converting
them to constant values otherwise; whereas, the manual solution
-- Edit / Paste Special / Multiply -- will retain the formula with the multiplier.

In fact before this actually went out noticed a similar question and
comment in another thread, where Tom Ogilvy responded with a similar remark.
http://google.com/groups?threadm=#[email protected]
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm