Multiply Input Cell by Another Cell

[menglish]

Hi There,

I'm trying to multiply a cell within a table that I would input a number into by another cell and struggling with the VBA formula.

For example, I would input 5600 into F2 and want it to multiply by D2 and display the result within F2.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B1[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]material[/TD]
[TD]Desc.[/TD]
[TD]Cube[/TD]
[TD]Lbs[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]XYZ[/TD]
[TD]Material[/TD]
[TD]190[/TD]
[TD]18[/TD]
[TD][/TD]
[TD]5600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC[/TD]
[TD]Material[/TD]
[TD]656[/TD]
[TD]4.375[/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks,

Matt

 

[Joe4]

I just helped someone with VBA code to replace a value entered as it is entered.
You should be able to take the same approach.
See here: https://www.mrexcel.com/forum/excel-questions/1109011-automatic-subtraction.html

If you need help, please provide more details, such as which ranges do you want to behave this way, and is it always the same cells/calculation?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("[COLOR=#ff0000]E2:K100[/COLOR]")) Is Nothing Then
      Target.Value = Target.Value * Cells(Target.Row, 4)
   End If
End Sub

This needs to go into the relevant sheet module.
Whenever you input a number into E2:K100 it will be multiplied by the value in col D for that row.
Change the range in red to suit.

 

[menglish]

I just helped someone with VBA code to replace a value entered as it is entered.
You should be able to take the same approach.
See here: https://www.mrexcel.com/forum/excel-questions/1109011-automatic-subtraction.html

If you need help, please provide more details, such as which ranges do you want to behave this way, and is it always the same cells/calculation?

I'm trying to build a weekly production planning tool for my team. There will be about 40 lines in total, which represents one SKU per line. Each row of in the range represents the days of the week as Sunday through Saturday, so the range would roughly be E2:K42. I want to be able to input an estimate number of units (the 5600 or 1000 in the example), and have that multiplied by the weight of the SKU in Column D to find the total weight of that particular SKU on that day.

 

[Joe4]

I'm trying to build a weekly production planning tool for my team. There will be about 40 lines in total, which represents one SKU per line. Each row of in the range represents the days of the week as Sunday through Saturday, so the range would roughly be E2:K42. I want to be able to input an estimate number of units (the 5600 or 1000 in the example), and have that multiplied by the weight of the SKU in Column D to find the total weight of that particular SKU on that day.

OK. I think Fluff's code should work for you then, if you just change the range in red to match your range (E2:K42).
Did you see if that does what you want?

 

[menglish]

OK. I think Fluff's code should work for you then, if you just change the range in red to match your range (E2:K42).
Did you see if that does what you want?

I did get it to work, but it comes up with an Out of Stack Space (Run Time Error 28) dialogue

 

[Joe4]

I think Fluff might have forgotten one important part to his code (or else you might caught in an infinite loop). Use this variation:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("E2:K42")) Is Nothing Then
      [COLOR=#ff0000]Application.EnableEvents = False[/COLOR]
      Target.Value = Target.Value * Cells(Target.Row, 4)
      [COLOR=#ff0000]Application.EnableEvents = True[/COLOR]
   End If
End Sub

I can't seem to get it to work. Where should I copy this into?

In the proper sheet module.

If you are unfamiliar with VBA and the VBA Project Explorer, here is an easy way to get there:
1. Go to the worksheet you want to apply this to
2. Right-click on the sheet tab name at the bottom of your screen
3. Select "View Code"
4. Copy and Paste the code above in the resulting VB Editor window that pops up

That is it! Try it out. As you manually enter values into range E2:K42, the code should automatically change your entries.

 

[Joe4]

I did get it to work, but it comes up with an Out of Stack Space (Run Time Error 28) dialogue

Yes, see the change I made to his code in my previous post.

 

[menglish]

That worked! Thank you!

 

[Joe4]

You are welcome.
Glad we were able to help.