VBA to Multiply Cell Value Automatically in Same Cell If Condition Met

[seanmic]

Hello,

I have a workbook where users are required to enter values in Range A2:A1000. If the value entered in the range is less than 100, I'd like the value to be automatically multiplied by 2080 in the exact same cell where it was typed. If, however, the user enters a value greater than or equal to 100, then the value would would be unchanged and would show in the cell exactly as typed.

Here's the code I currently have... could someone please help me to modify it so it can handle the criteria above?

Private changeFlag As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 1 And Not changeFlag Then
        changeFlag = True
        intcolumn = Target.Column
        introw = Target.Row
        Cells(introw, intcolumn).Value = Cells(introw, intcolumn).Value * 2080
    Else
        changeFlag = False
    End If
End Sub

Thanks in advance for your help
~Sean

 

[JLGWhiz]

Hi Rosher, I normally would not respond to a tag on to a two year old thread, but since you are a new member, I will give you some advice on getting help quickly and efficiently. Start your own thread with a clear explanation of what your objective is. Post your code, as you have done here, if applicable and post screen shots of your worksheet using the xl2BB tool when applicable. If you have found a previous thread, like this one, that you think is helpful in explaining your problem, then reference with a link. But piggy backing onto a thread prevents a lot of forum members from seeing your particular problem, the discussions of it and solutions to it. In summary, you need to start a new thread.

 

[Rosher]

Hi Rosher, I normally would not respond to a tag on to a two year old thread, but since you are a new member, I will give you some advice on getting help quickly and efficiently. Start your own thread with a clear explanation of what your objective is. Post your code, as you have done here, if applicable and post screen shots of your worksheet using the xl2BB tool when applicable. If you have found a previous thread, like this one, that you think is helpful in explaining your problem, then reference with a link. But piggy backing onto a thread prevents a lot of forum members from seeing your particular problem, the discussions of it and solutions to it. In summary, you need to start a new thread.

Ok thanks for the info. Will post a new thread.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/5/2020  12:06:44 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 * Target.Offset(, 1).Value): Target.Value = ans
End If
Application.EnableEvents = True
End If
End Sub

 

[Rosher]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/5/2020  12:06:44 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 * Target.Offset(, 1).Value): Target.Value = ans
End If
Application.EnableEvents = True
End If
End Sub

Thanks for your help but my query was resolved.