# Send multiply values to the same cell

##### Well-known Member
possible tricky [well for me anyway]

Part1: say I have
a1= 50
a2=300

How could I write a [button] macro that when run would take any cell from a defined range. in this case cells to E1 an added them together so that E1=350.

Part2: would be to determine what cell it goes to base on a value in B1.
so that if
B1 =1 then E1 if
B1 =2 then E2 etc....

Part3: [If possible] If the macro was repeated with the new values they would be added to the total already in E1, ie if a1=60, then E1=410 [350+60]

The purpose is that depending on a line number [B1] send the costs of items to the right department.

hope someone can throw me a bone on this one. Cheers

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Anthony47

##### Well-known Member
Not clear which are the input and which is the output; not to me...
Whould you like to put the value A1+A2 in the cell that result from offsetting E1 by the value in B1 minus 1?

Code:
``````Sub CadTest()
Range("E1").Offset(Range("B1").Value - 1, 0).Value = Range("A1").Value + Range("A2").Value
End Sub``````

Then assign a shortcut to this macro (Menu /Tools /Macro /Macro; select CadTest fom the list, press Option, set Q (upper case) as shortcut, Ok, close the Macro window); in this way the macro will run whenever you press Contr-Shift-q
For the third point: if the above guess is correct then I did not understand it, a clarification would help.

Bye.

##### Well-known Member
Anthony Awesome

For the 3rd part: I amended it so that it could include any values already in the E column

Range("E1").Offset(Range("B1").Value - 1, 0).Value = Range("A1").Value + Range("A2").Value + Range("E1").Offset(Range("B1").Value - 1, 0)
End Sub

Thanks for your input, I'm going to play around and see if I can implement the theory of it.

ciao

Replies
6
Views
237
Replies
2
Views
366
Replies
2
Views
349
Replies
0
Views
653
Replies
2
Views
490

1,190,874
Messages
5,983,341
Members
439,839
Latest member
iblackie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back