Pulling my hair out trying to figure out why my IF statements are not working correctly....

[Mixphonics]

Hello, I am hoping someone can help me with the code I have below. I am trying to figure out what is wrong with my syntax.

The particular part of the code I am having problems with is this part:

If Corktype = "agglomerated" Then


ElseIf InitQty < 2 Then ReqTests = 0
ElseIf InitQty <= 15 And InitQty >= 2 Then ReqTests = 2
ElseIf InitQty <= 25 And InitQty > 15 Then ReqTests = 3
ElseIf InitQty <= 90 And InitQty > 25 Then ReqTests = 5
ElseIf InitQty <= 150 And InitQty > 90 Then ReqTests = 5
ElseIf InitQty <= 280 And InitQty > 150 Then ReqTests = 8
ElseIf InitQty > 281 Then ReqTests = 13

Else
End If




If Corktype = "natural" Then


ElseIf InitQty < 2 Then ReqTests = 0
ElseIf InitQty <= 8 And InitQty >= 2 Then ReqTests = 2
ElseIf InitQty <= 15 And InitQty > 8 Then ReqTests = 3
ElseIf InitQty <= 25 And InitQty > 15 Then ReqTests = 5
ElseIf InitQty <= 50 And InitQty > 25 Then ReqTests = 8
ElseIf InitQty <= 90 And InitQty > 50 Then ReqTests = 13
ElseIf InitQty <= 150 And InitQty > 90 Then ReqTests = 20
Else
End If

I've tried messing around with it in different ways including UCase("agglomerated") etc... and doing a instr(corktype, "natural") but the vba code seems to step right through the fact that if the variable Corktype = "natural" and the code says If Corktype = "agglomerated" then it continues through the code even though the variable Corktype does not equal "agglomerated".

Any suggestions?

I have included the entire Macro below for anyone's review.

Best regards,

Mixphonics





Sub Macro()
'Global Variables
Dim QtyTests As Integer, ReqTests As Integer, InitQty As Integer, Cork As String, Corktype As String
Dim result As Integer


'select starting position of macro
Sheets("ETS Testing").Select
Range("G4").Select




'Bale Qty for each grade
InitQty = ActiveCell.Offset(0, 5).Value
'Corkgrade & type for each lot/grade
Cork = ActiveCell.Offset(0, 1).Value
'define the range of the 20 possible bales tested at ETS's results
Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)).Select
QtyTests = WorksheetFunction.CountA(Range(ActiveCell.Offset(0, 8), ActiveCell.Offset(0, 27)))
ActiveCell.Offset(0, -8).Select
result = InStr(Cork, "C3")


If result = 0 Then Corktype = "natural" Else Corktype = "agglomerated"


'This is the start of the problem code, even though Corktype is "natural is still proceeds through if statement

If Corktype = "agglomerated" Then


ElseIf InitQty < 2 Then ReqTests = 0
ElseIf InitQty <= 15 And InitQty >= 2 Then ReqTests = 2
ElseIf InitQty <= 25 And InitQty > 15 Then ReqTests = 3
ElseIf InitQty <= 90 And InitQty > 25 Then ReqTests = 5
ElseIf InitQty <= 150 And InitQty > 90 Then ReqTests = 5
ElseIf InitQty <= 280 And InitQty > 150 Then ReqTests = 8
ElseIf InitQty > 281 Then ReqTests = 13

Else
End If




If Corktype = "natural" Then


ElseIf InitQty < 2 Then ReqTests = 0
ElseIf InitQty <= 8 And InitQty >= 2 Then ReqTests = 2
ElseIf InitQty <= 15 And InitQty > 8 Then ReqTests = 3
ElseIf InitQty <= 25 And InitQty > 15 Then ReqTests = 5
ElseIf InitQty <= 50 And InitQty > 25 Then ReqTests = 8
ElseIf InitQty <= 90 And InitQty > 50 Then ReqTests = 13
ElseIf InitQty <= 150 And InitQty > 90 Then ReqTests = 20
Else
End If







End Sub

 

[mvptomlinson]

Try it this way:

If Corktype = "agglomerated" Then
    If InitQty < 2 Then
        ReqTests = 0
    ElseIf InitQty <= 15 And InitQty >= 2 Then
        ReqTests = 2
    ElseIf InitQty <= 25 And InitQty > 15 Then
        ReqTests = 3
    ElseIf InitQty <= 90 And InitQty > 25 Then
        ReqTests = 5
    ElseIf InitQty <= 150 And InitQty > 90 Then
        ReqTests = 5
    ElseIf InitQty <= 280 And InitQty > 150 Then
        ReqTests = 8
    ElseIf InitQty > 281 Then
        ReqTests = 13
    End If
End If

If Corktype = "natural" Then
    If InitQty < 2 Then
        ReqTests = 0
    ElseIf InitQty <= 8 And InitQty >= 2 Then
        ReqTests = 2
    ElseIf InitQty <= 15 And InitQty > 8 Then
        ReqTests = 3
    ElseIf InitQty <= 25 And InitQty > 15 Then
        ReqTests = 5
    ElseIf InitQty <= 50 And InitQty > 25 Then
        ReqTests = 8
    ElseIf InitQty <= 90 And InitQty > 50 Then
        ReqTests = 13
    ElseIf InitQty <= 150 And InitQty > 90 Then
        ReqTests = 20
    End If
End If

When you have the

If (this) THEN (that)

on one line, it completes the entire IF function, so you can't use ElseIf to continue with the test. If you use this:

If (this) Then
    (that)
ElseIf (another) Then
    (something)
End If

you can.

 

[rollis13]

To be of some help:

this should be:
ElseIf InitQty >= 281 Then

instead of:
ElseIf InitQty > 281 Then

elsewise it will not test 281.

 

[Mixphonics]

Thank you mvptomlinson! I've spent hours trying to figure out why my code wasn't working and your coding solved the issue.

Awesome job!

Also, rollis13 thank you for catching that error, I will fix the math.

I will definately be using this board in the future for any further vba excel problems. You guys are great.




Best regards,

Mixphonics

 

[p45cal]

for the blue part of your question try:

Select Case Corktype
    Case "agglomerated"
        ReqTests = Application.Lookup(InitQty - 0.1, Array(-100, 2, 15, 25, 90, 150, 280), Array(0, 2, 3, 5, 5, 8, 13))
    Case "natural"
        ReqTests = Application.Lookup(InitQty - 0.1, Array(-100, 2, 8, 15, 25, 50, 90, 150), Array(0, 2, 3, 5, 8, 13, 20))
End Select

You may have to tweak the 280/281

 

[Mixphonics]

Thank you p45cal,

I am unfamiliar with the command select case so I will look further into it. Can I simply replace the blue part of the code with what you have coded for me?

I see what you did as far as setup an array, very advanced coding. I'll have to try that copy and paste it to see how it compares to the code I have now.

Thank you very much for taking the time to help.

Best regards,

Mixphonics

 

[Mixphonics]

Thank you Rollis, I've added the = sign to include 281 now.

 

[rollis13]

Glad being of some help .

 

[p45cal]

Can I simply replace the blue part of the code with what you have coded for me?

Yes.