Is it possible to multiply an arrange of numbers by a cell?
For example:
A B
1 x
2
3
4
5
Is there a way to multiply A2:A6 by whatever value is in B2? I want to be able to change B2 and have the numbers in A2:A6 change.
Thanks,
Bob
Is it possible to multiply an arrange of numbers by a cell?
For example:
A B
1 x
2
3
4
5
Is there a way to multiply A2:A6 by whatever value is in B2? I want to be able to change B2 and have the numbers in A2:A6 change.
Thanks,
Bob
Try
=SUMPRODUCT(A2:A6)*B2
VBA Noob
_________________________________________
Credo Elvem ipsum etian vivere
_________________________________________
A message for cross posters
Please remember to wrap code.
Forum Rules
Please add to your signature if you found this link helpful. Excel links !!!
Hi,
Do you mean =A2*$B$2 ... in cell C2 ...
and copied down ... ?
HTH
Carim
That's not what I meant.
I want to multiply the numbers in the array by the cell, where the numbers in the array don't change but the number in the single cell can. If C2 = 2, then I would want A to look like...
A B
2 2
4
6
8
10
If I changed C2 to be 3, then I would want A to look like...
A B
6 3
9
12
15
18
I don't care if the results of the multiplication are in a new array of cells eg.
A B C
1 6 3
2 9
3 12
4 15
5 18
Here A2:A6 is multiplied by the value of C2, and the results are displayed in B2:B6 instead of replacing the values of A2:A6.
Thanks,
Bob
Last edited by Table10sHotShot; 11-26-2006 at 03:51 PM.
Nevermind I got it working with the $'s... thanks guys.
Thanks,
Bob
Hi,
You can also get it done with an event macro
HTHPrivate Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer, NewVal As Double Dim LR LR = Cells(Cells.Rows.Count, "A").End(xlUp).Row If Target.Count > 1 Then Exit Sub If Target.Address = "$B$1" Then Application.EnableEvents = False NewVal = Range("B1").Value For i = 0 To LR - 1 Target.Offset(i, -1).Value = NewVal * Target.Offset(i, -1).Value Next i Application.EnableEvents = True Else Exit Sub End If End Sub
Carim
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks