VBA code to Multiply Cell Value In First Cell Automatically With Cell Value In Another Cell And Get Result In Same Cell

Rosher

New Member
Joined
Mar 5, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
My query is similar to the original poster here - VBA to Multiply Cell Value Automatically in Same Cell If Condition Met
but there is slight difference in my case. I want the Range A2:A1000 to be multiplied but not by a fixed value say
2080(in that case) but i want the Range A2:A1000 to be multiplies by values in range B2:B1000. I tried and found one of the above code is working find for fixed value but not working for my query. This is the code which i have tried :

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-1-18 8:30 PM EST
If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
Application.EnableEvents = False
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If IsNumeric(Target.Value) Then
Dim ans As Long
ans = Target.Value
If ans < 100 Then ans = (ans * 2080): Target.Value = ans
End If
Application.EnableEvents = True
End If
End Sub

Please help me, how can i get the values in Range A2:A1000 to be multiplies by values in range B2:B1000. I have Item cost price mentioned in column A and Qty mentioned in column B. Currently the qty in B column is fixed to 1. So what i want is - whenever the qty in B column increases (say from 1 to 2) the cost in column should increase too ie A2*B2. So in short i want, A2=A2*B2. So now if value in B2 changes, A2 should change automatically.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,691
Office Version
  1. 365
Platform
  1. Windows
@Fluff beat me to it! Was trying something with application.undo but it was EnableEvents kept being triggered (despite set to False), glad you have a working solution anyway :)
 

Rosher

New Member
Joined
Mar 5, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
@Fluff beat me to it! Was trying something with application.undo but it was EnableEvents kept being triggered (despite set to False), glad you have a working solution anyway :)
Thanks to you too for trying to help me out but @Fluff code is working fine for me.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,634
Members
410,861
Latest member
Victor96
Top