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

"Sherri Baker" wrote in message
...
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,

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

-----Original Message-----
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 Baker" wrote in
message
...
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.


.

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.

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

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



"Sherri Baker" wrote in message
...
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.

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" wrote in message
...
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

-----Original Message-----
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 Baker" wrote in
message
...
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.


.

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 /
Mutiply.

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

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



"Sherri Baker" wrote in
message
...
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,

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

Sherri
-----Original Message-----
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" wrote in message
...
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

-----Original Message-----
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 Baker" wrote in
message
...
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.


.



.

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

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

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



"Sherri" wrote in message
...
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 /
Mutiply.

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

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



"Sherri Baker" wrote in
message
...
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.


.

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=%23...gp13.phx. gbl
---
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


"Sherri" wrote
What an amazingly simple solution. Thanks!

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

"Sherri Baker" wrote in
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.

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
----------------------------------------------------------------------------



"David McRitchie" wrote in message
...
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=%23...gp13.phx. gbl
---
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


"Sherri" wrote
What an amazingly simple solution. Thanks!

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

"Sherri Baker" wrote in
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.