Excel 2002 - Multiplication

  • Thread starter Thread starter Sherri Baker
  • Start date Start date
S

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.
 
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
 
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
 
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.
 
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
 
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.
Click to expand...
Assuming you wanted to mutiply them all by
say 6, then put 6 in a cell, copy it, select the range
Click to expand...
of data and do Edit / Paste Special /
 
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
 
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]
 
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
 
Back
Top