VBA Userform If Statement Problem

[AC1982]

Hi Guys,

I have created a Userform that calculates mark-ups and gross margin based on a cost price and sell price.

My problem is that when I enter a sell price into the textbox that is less than the cost price, it shows me a positive mark-up but a negative gross margin.

In this scenario I need it to show me a negative mark-up because the desired sell price is less than the cost price.

All numbers are formatted in currency.

Can anyone help me, I think the problem is in If statement "If Me.RequiredSellPrice.Value >= Me.CurrentCost.Value Then"

Because let's say Me.RequiredSellPrice.Value = $95 and Me.CurrentCost.Value = $100 , my code will treat it as having agreed with the If statement and $95 is not greater than $100

Here is my code below, can anyone see the problem ?

Private Sub RequiredSellPrice_AfterUpdate()
If IsNumeric(Me.SSRRef) Then
If Me.CurrentCostPrice.Text = "$0 in SSR Sheet" Then
Exit Sub
Else
If IsNumeric(Me.RequiredSellPrice.Value) Then
Me.RequiredSellPrice = FormatCurrency(Me.RequiredSellPrice, 2)
If Me.RequiredSellPrice.Value >=Me.CurrentCostPrice.Value Then
Me.Materials1 = FormatPercent(Abs(((Abs(Me.RequiredSellPrice.Value- Range("AJ" & Me.SSRRef + 9) _
- Range("AM" & Me.SSRRef + 9)) / Me.CurrentCostPrice.Value )) - 1), 5)
Me.Materials1.Object.Locked = True
Me.Materials1.BackColor = RGB(192, 192, 192)
Me.GMMat1.Value = (Me.RequiredSellPrice.Value - Me.CurrentCostPrice.Value ) / Me.CurrentCostPrice.Value
Me.GMMat1.Value = FormatPercent(Me.GMMat1.Value / (1 + Me.GMMat1.Value), 4)
Else
Me.Materials1 = FormatPercent(((Me.RequiredSellPrice.Value  - Range("AJ" & Me.SSRRef + 9) _
- Range("AM" & Me.SSRRef + 9)) / Me.CurrentCostPrice.Value) + 1, 5)
Me.Materials1.Object.Locked = True
Me.Materials1.BackColor = RGB(192, 192, 192)
Me.GMMat1.Value = (Me.RequiredSellPrice.Value -  Me.CurrentCostPrice.Value) / Me.CurrentCostPrice.Value
Me.GMMat1.Value = FormatPercent(Me.GMMat1.Value / (1 + Me.GMMat1.Value), 4)
End If
Else
Me.Materials1 = ""
Me.Materials1.Object.Locked = False
Me.Materials1.BackColor = RGB(255, 255, 255)
Me.GMMat1.Value = ""
End If
End If
End If
End Sub

Thanks.

 

[AMAS]

Hi,

Your problem doesn't seem to be with the IF statement but rather what to do if the result is FALSE:

Option Explicit

Private Sub RequiredSellPrice_AfterUpdate()

If IsNumeric(Me.SSRRef) Then
    If Me.CurrentCostPrice.Text = "$0 in SSR Sheet" Then
        Exit Sub
    Else
        If IsNumeric(Me.RequiredSellPrice.Value) Then
            Me.RequiredSellPrice = FormatCurrency(Me.RequiredSellPrice, 2)
                If Me.RequiredSellPrice.Value >= Me.CurrentCostPrice.Value Then
                ' This is what you will do if the price is correct
                    Me.Materials1 = FormatPercent(Abs(((Abs(Me.RequiredSellPrice.Value - Range("AJ" & Me.SSRRef + 9) _
                      - Range("AM" & Me.SSRRef + 9)) / Me.CurrentCostPrice.Value)) - 1), 5)
                    Me.Materials1.Object.Locked = True
                    Me.Materials1.BackColor = RGB(192, 192, 192)
                    Me.GMMat1.Value = (Me.RequiredSellPrice.Value - Me.CurrentCostPrice.Value) / Me.CurrentCostPrice.Value
                    Me.GMMat1.Value = FormatPercent(Me.GMMat1.Value / (1 + Me.GMMat1.Value), 4)
                Else
                ' This should be different than above - need to change this
'                    Me.Materials1 = FormatPercent(((Me.RequiredSellPrice.Value - Range("AJ" & Me.SSRRef + 9) _
'                      - Range("AM" & Me.SSRRef + 9)) / Me.CurrentCostPrice.Value) + 1, 5)
'                    Me.Materials1.Object.Locked = True
'                    Me.Materials1.BackColor = RGB(192, 192, 192)
'                    Me.GMMat1.Value = (Me.RequiredSellPrice.Value - Me.CurrentCostPrice.Value) / Me.CurrentCostPrice.Value
'                    Me.GMMat1.Value = FormatPercent(Me.GMMat1.Value / (1 + Me.GMMat1.Value), 4)
                End If
        Else
            Me.Materials1 = ""
            Me.Materials1.Object.Locked = False
            Me.Materials1.BackColor = RGB(255, 255, 255)
            Me.GMMat1.Value = ""
        End If
    End If
End If

End Sub

The TRUE and FALSE reactions are exactly the same. You need to do something different (I guess) if the sale price is less than the cost price.

Hope this helps.

AMAS

P.S. I commented out the section that should be modified (I think).

 

[AC1982]

Hi,

Your problem doesn't seem to be with the IF statement but rather what to do if the result is FALSE:

Option Explicit
 
Private Sub RequiredSellPrice_AfterUpdate()
 
If IsNumeric(Me.SSRRef) Then
    If Me.CurrentCostPrice.Text = "$0 in SSR Sheet" Then
        Exit Sub
    Else
        If IsNumeric(Me.RequiredSellPrice.Value) Then
            Me.RequiredSellPrice = FormatCurrency(Me.RequiredSellPrice, 2)
                If Me.RequiredSellPrice.Value >= Me.CurrentCostPrice.Value Then
                ' This is what you will do if the price is correct
                    Me.Materials1 = FormatPercent(Abs(((Abs(Me.RequiredSellPrice.Value - Range("AJ" & Me.SSRRef + 9) _
                      - Range("AM" & Me.SSRRef + 9)) / Me.CurrentCostPrice.Value)) - 1), 5)
                    Me.Materials1.Object.Locked = True
                    Me.Materials1.BackColor = RGB(192, 192, 192)
                    Me.GMMat1.Value = (Me.RequiredSellPrice.Value - Me.CurrentCostPrice.Value) / Me.CurrentCostPrice.Value
                    Me.GMMat1.Value = FormatPercent(Me.GMMat1.Value / (1 + Me.GMMat1.Value), 4)
                Else
                ' This should be different than above - need to change this
'                    Me.Materials1 = FormatPercent(((Me.RequiredSellPrice.Value - Range("AJ" & Me.SSRRef + 9) _
'                      - Range("AM" & Me.SSRRef + 9)) / Me.CurrentCostPrice.Value) + 1, 5)
'                    Me.Materials1.Object.Locked = True
'                    Me.Materials1.BackColor = RGB(192, 192, 192)
'                    Me.GMMat1.Value = (Me.RequiredSellPrice.Value - Me.CurrentCostPrice.Value) / Me.CurrentCostPrice.Value
'                    Me.GMMat1.Value = FormatPercent(Me.GMMat1.Value / (1 + Me.GMMat1.Value), 4)
                End If
        Else
            Me.Materials1 = ""
            Me.Materials1.Object.Locked = False
            Me.Materials1.BackColor = RGB(255, 255, 255)
            Me.GMMat1.Value = ""
        End If
    End If
End If
 
End Sub

The TRUE and FALSE reactions are exactly the same. You need to do something different (I guess) if the sale price is less than the cost price.

Hope this helps.

AMAS

P.S. I commented out the section that should be modified (I think).

Thanks for the feedback but the True and False statements are different.

If the Sell Price is Greater than The Cost Price then

 Me.Materials1 = FormatPercent(((Me.RequiredSellPrice.Value - Range("AJ" & Me.SSRRef + 9) _
'                      - Range("AM" & Me.SSRRef + 9)) / Me.CurrentCostPrice.Value) - 1, 5)

If the Cost Price is Greater than the Sell Price then

 Me.Materials1 = FormatPercent(((Me.RequiredSellPrice.Value - Range("AJ" & Me.SSRRef + 9) _
'                      - Range("AM" & Me.SSRRef + 9)) / Me.CurrentCostPrice.Value) + 1, 5)

I have ran the code in debug mode and it won't recognise

  If Me.RequiredSellPrice.Value >= Me.CurrentCostPrice.Value

As being false when the Sell Price is less than the Cost Price

 

[AC1982]

If I type in a sell price of $1000.00 and the currenct cost is $100,000.00 the code above works

However if I type in a sell price of $95,000.00 and the currenct cost is $100,000.00 the code doesn't work and treats the following statement as being TRUE

If Me.RequiredSellPrice.Value >= Me.CurrentCostPrice.Value Then

When it should be FALSE

 

[AMAS]

Hi,

I can't see what is going wrong. For example, here is a simple example:

Sub tryme()
Dim x As Integer
Dim y As Integer

x = 1000
y = 10000
If x >= y Then
    MsgBox ("x more")
Else
    MsgBox ("y more")
End If
End Sub

This seems to work.

Have you tried as a Msgbox to see what the actual values being passed are (e.g. Msgbox(Me.RequiredSellPrice.Value))?

AMAS

 

[AMAS]

Also one last thought. Have you tried to comment out this line and see if its causing the problem:

Me.RequiredSellPrice = FormatCurrency(Me.RequiredSellPrice, 2)

The reason I am asking is becuase you are formatting the SellPrice but not the CostPrice.

Finally, are you receiving any errors messages (assuming that you don't have On Error Resume Next coded into the Sub)?

AMAS