CODEWITHSUNDEEP
excelif-statementvba
Putaru Andreea
Putaru Andreea

Reputation: 11

How can I use elseif to fix my code?

I have this Excel VBA code that should tell me which sum to pay based on the product, but wether I type "credit" or any other product it skips the first IF statement and gets to the second one.

I'd like to know how I can use else if so it will genarate the right sum.

Here is the code:

Dim prod As String
Dim sumam As Integer
Dim total As Integer
Dim rate As Integer

prod = InputBox("Introduceti tipul de produs")
total = InputBox("Introduceti suma totala de plata")

If prod = credit Then
   If total < 1000 Then sumam = 175
   If total > 1000 And total < 3000 Then sumam = 350
   If total > 3000 And total < 6000 Then sumam = 425
Else
   If total < 1000 Then sumam = 150
   If total > 1000 And total < 2000 Then sumam = 200
   If total > 2000 And total < 5000 Then sumam = 325
   If total > 5000 And total < 8000 Then sumam = 450
   If total > 8000 And total < 12000 Then sumam = 550
   If total > 12000 Then sumam = 675
rate = total / sumam

textprod.Text = prod
textprod.TextAlign = fmTextAlignRight
texttot.Text = total
texttot.TextAlign = fmTextAlignRight
textsumm.Text = sumam
textsumm.TextAlign = fmTextAlignRight
textrate.Text = rate
textrate.TextAlign = fmTextAlignRight

End If
End Sub

Upvotes: 1

Views: 102

Answers (5)

James Heffer
James Heffer

Reputation: 742

[Untested]

Hi Putaru, try including double quotes on credit. Like this: 

If prod = "credit" Then
   If total < 1000 Then sumam = 175
   If total > 1000 And total < 3000 Then sumam = 350
   If total > 3000 And total < 6000 Then sumam = 425
   Else...

I'm not sure what textprod is? Not sure if that's an object or not, but adding in double quotes should let that if statement check a value, and not a variable called credit, and instead it will check for a string named "credit" with the double quotes included in the code.

Hope this works, as I said, this is untested...

Because the way you have it like this: 

If prod = credit Then
   If total < 1000 Then sumam = 175
   If total > 1000 And total < 3000 Then sumam = 350
   If total > 3000 And total < 6000 Then sumam = 425
Else

And it's looking for a variable called credit, which you don't declare. Happy coding :)

EDIT: 

Option Explicit

Sub test()
Dim prod As String
Dim sumam As Integer
Dim total As Integer
Dim rate As Integer

prod = InputBox("Enter credit or cheque")
total = InputBox("Enter total")
sumam = 0
rate = 0

If prod = "credit" Then
   If total < 1000 Then sumam = 175
   If total > 1000 And total < 3000 Then sumam = 350
   If total > 3000 And total < 6000 Then sumam = 425
Else
   If total < 1000 Then sumam = 150
   If total > 1000 And total < 2000 Then sumam = 200
   If total > 2000 And total < 5000 Then sumam = 325
   If total > 5000 And total < 8000 Then sumam = 450
   If total > 8000 And total < 12000 Then sumam = 550
   If total > 12000 Then sumam = 675
End If
rate = total / sumam
MsgBox (rate)
End Sub

Tested this, and it doesn't return 0 anymore, it will display in a message box, your value.

Upvotes: 1

Sgdva
Sgdva

Reputation: 2800

The common cause for this are trims, mix of U/lcase (Since it's an input for the user he may have type as he feels to). Have you tried?:

If lcase(Trim(prod)) = "credit" Then

Upvotes: 0

Gary Evans
Gary Evans

Reputation: 1890

Using ElseIf is another way to do what you are doing but it doesn't have to be the fix. Below is an ElseIf Example: -

Public Sub ElseIfSample()

If total < 1000 Then
    sumam = 175
ElseIf total > 1000 And total < 3000 Then
    sumam = 350
ElseIf total > 3000 And total < 6000 Then
    sumam = 425
End If

End Sub

But this and your previous code will both have the same issue of missing values 1000, 3000, and anything over 5999

The below code would fix that along with some other small changes: -

Public Sub Sample()
Dim LngSumam    As Long
Dim LngRate     As Long
Dim StrProd     As String
Dim StrTotal    As String

StrProd = InputBox("Introduceti tipul de produs")
StrTotal = InputBox("Introduceti suma totala de plata")

If Not IsNumeric(StrTotal) Then Exit Sub

If Trim(UCase(StrProd)) = "CREDIT" Then
    If CLng(StrTotal) >= 3000 Then       'Total equal to or above 3000
        LngSumam = 425
    ElseIf CLng(StrTotal) >= 1000 Then   'Total equal to or above 1000
        LngSumam = 350
    Else                                'All other cases
        LngSumam = 175
    End If
Else
    If CLng(StrTotal) >= 12000 Then       'Total equal to or above 12000
        LngSumam = 675
    ElseIf CLng(StrTotal) >= 8000 Then   'Total equal to or above 8000
        LngSumam = 550
    ElseIf CLng(StrTotal) >= 5000 Then   'Total equal to or above 5000
        LngSumam = 550
    ElseIf CLng(StrTotal) >= 2000 Then   'Total equal to or above 2000
        LngSumam = 325
    ElseIf CLng(StrTotal) >= 1000 Then   'Total equal to or above 1000
        LngSumam = 200
    Else                                'All other cases
        LngSumam = 200
    End If

LngRate = CLng(StrTotal) / LngSumam

textprod.Text = StrProd
textprod.TextAlign = fmTextAlignRight
texttot.Text = StrTotal
texttot.TextAlign = fmTextAlignRight
textsumm.Text = LngSumam
textsumm.TextAlign = fmTextAlignRight
textrate.Text = LngRate
textrate.TextAlign = fmTextAlignRight

End If

End Sub
  • Used Long instead of Integer as Long handles bigger numbers easier,
  • Changed the input variables to String to ensure less errors

You could have also used nested If statements or Select Case.

NOTE: The text fields in your cod only get filled in if the user doesn't enter 'Credit' in the first input box, is this correct?

Upvotes: 0

Syntax Error
Syntax Error

Reputation: 1640

Have you tried this;

If prod = "credit" Then

Note the quotes around the word credit.

Upvotes: 0

Purvendra Kumar
Purvendra Kumar

Reputation: 3

I think you are missing double quotes... You should use prod = "credit" instead of prod = credit

Upvotes: 0

Related Questions