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.
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.