VB Coding Help

[samsilverman]

Need some help with vb coding.

What I have now is
If Target >= 101 Then Target = Target * 1.14
If Target <= 100 Then Target = Target * 1.25

What I really need is
If Target >0 but <=100 then Target = Target * 1.25
If Target >100 but <500 then Target = Target * 1.14
If Target >=500 but then Target = Target * 1

Can anyone help me code this correctly? Have tried for about a half hour unsuccessfully.

Using Excel 2007

 

[Tom Urtis]

Just to clarify, is this for a Change event or a selection event or what...Target sounds like it's an event of some kind and it's important to avoid an infinite loop if it's a Change event.

Edit, why are you multiplying by 1? All you will end up with is the same number.

 

[samsilverman]

Sorry. Here's the whole code as it is now

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range, MyRange As Range
    
    If Target.Cells.Count = 1 And Target.HasFormula = False Then
        On Error Resume Next
        Application.EnableEvents = False
        If InStr(Target.Value, "@") = 0 Then
            Target = UCase(Target)
        End If
    
        If Not Intersect(Target, Range("C:C, E:E")) Is Nothing Then
            Select Case Target.Column
                Case 5
                    If Target > 0 Then Target = -Target
                Case 3
                    If Target >= 101 Then Target = Target * 1.14
                    If Target <= 100 Then Target = Target * 1.25
            End Select
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[Tom Urtis]

The code you posted is different than the scenario you described in your first post. Which is the correct criteria. Also, did you see my edit question, why are you multiplying by 1 which will only give you the same number you started with.

This is the general syntax for a solution, based on your first post:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
Select Case .Value
Case Is <= 100: .Value = .Value * 1.25
Case Is < 500: .Value = .Value * 1.14
Case Else: .Value = .Value * 1
End Select
End With
Application.EnableEvents = True
End Sub

 

[samsilverman]

Edit, why are you multiplying by 1? All you will end up with is the same number.

Yeah. Just trying anything to get the result I wanted. Was trying all kinds of stuff.

I just wanted this for Case 3. What about the -target for case 5? Sorry my original post was misleading.

 

[Tom Urtis]

The column portion of your code could be expressed as follows, if I understand the issue:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target

If .Column = 3 Then

Select Case .Value
Case Is <= 100: .Value = .Value * 1.25
Case Is < 500: .Value = .Value * 1.14
Case Else: .Value = .Value * 1
End Select

ElseIf .Column = 5 Then
If .Value > 0 Then .Value = -.Value

End If

End With
Application.EnableEvents = True
End Sub

 

[p45cal]

try:

target.Value = target.Value * Application.Index(Array(1, 1.14, 1.25, 0), Application.Match(target.Value, Array(9E+99, 500, 100, 0), -1))

It's not quite the same, the difference being in red below, and missing = in the line below it.:

If Target >0 but <=100 then Target = Target * 1.25
If Target >100 but <=500 then Target = Target * 1.14
If Target > 500 but then Target = Target * 1

but you can adjust the 500 in the code to say 499.999 to cater for that.

 

[samsilverman]

Thank you both for your help. Tom's solution worked great.

 

[p45cal]

or a bit shorter (untested):

target.Value = Choose(Application.Match(target.Value, Array(9E+99, 500, 100, 0), -1), 1, 1.14, 1.25, 0)