formulas in excel

March 31, 2009 at 12:19:32
Specs: Windows XP
is there a way to make a value in a particular cell multiply in other cells various amounts of times by using one standard formula for all cells

See More: formulas in excel

Report •

March 31, 2009 at 12:36:09
Please explain what you are trying to do.

Report •

March 31, 2009 at 13:12:55
I am trying to have values populate at different increments in their cell by only keying in said increment. For instance if column A row 1 is equal to 100, then i want to be able to get colum B row 1 to equal 200 by only typing in the number 2 in that particular cell.

Report •

March 31, 2009 at 13:56:55
This will multiply whatever is entered in any cell by whatever is in A1. It should probably be refined so that it is limited to certain cells, certain inputs, etc. but it'll get you started.

If you enter text in a cell, it will be ignored.

Right-Click the sheet tab, choose View Code and paste it in.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo NotNum
 Application.EnableEvents = False
    NewNum = Target * Range("A1")
    Target = NewNum
  Application.EnableEvents = True
End Sub

Report •

Related Solutions

March 31, 2009 at 16:11:51
Thank you but when I right click on the view code tab I am not sure what I am pasting in or where...please advise.

Report •

March 31, 2009 at 17:32:52
- Right click the sheet tab.
- The bottom choice should be View Code (at least in Version 2003 and earlier)
- Click View Code
- A blank VBA Editor window should open.
- Paste the code from my earlier post into the window. (Copy everything from the line that starts with Private to the one that say End Sub)

This is known as a macro, specifically an event macro that will run whenever there is a change to the spreadsheet. The reason you need to use a macro is because you can't put a formula in a cell and then enter a number in the same cell - the number would simply overwrite the formula.

What that particular macro does is this:

1 - Read whatever you put into a cell (the "Target")
2 - Multiply that number by whatever is in A1.
3 - Store the result in a variable called NewNum
4 - Place the value of NewNum back into the Target cell.

The error code is so that the code doesn't bomb if you put text in a cell.

However, the problem you are going to have is that the code is going to run regardless of which cell you enter data in.

So let's say you put a 2 in A1. Since the code doesn't care which cell you changed, it's going to to read the 2, then multiply it by what is in A1 (2) and you'll end up with 4.

So now you have a 4 in A1. Put a 3 in B1 and you'll get 12.

Now in C1 put =A1+B1

While you would like the answer to be 16 (4+12), you are going to be disappointed. It's going to be 64 since the entering of the formula will trigger the change and the answer will be 16 for a split second until the code multiplies it by A1 (4) and replaces the formula with number 64.

So what you need to do is tell the Event macro exactly what range you actually want it to change instead of letting it change every cell in the workbook. Give me some details and I'll see if I can make that happen for you.

Report •

April 1, 2009 at 08:29:52
wow that worked sort of but is there a way to have several macros running on the same worksheet? for instance, if i have different values down the A column that i want to multiply out on different rows can that happen. like say if A2 = 250 and i want the multiplication to happen on row 2 can that be achieved?

Report •

April 1, 2009 at 09:40:26
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo NotNum
'Allow entries into Column A
   If Target.Column = 1 Then Exit Sub
'Disable Events
   Application.EnableEvents = False
'Multiply Target by value in Column A, same Row
   NewNum = Target * Range("A" & Target.Row)
'Place new value in cell
   Target = NewNum
'Enable Events
   Application.EnableEvents = True
End Sub

Report •

April 1, 2009 at 09:56:42
For some reason now the values are not multiplying out in column A (which is actually my column B but I changed the macro and replaced all of the A's with B's) Any suggestions?

Report •

Ask Question