I'm pretty new to VBA and I have been absolutely bewildered on a portion of a subroutine I have for close to 2 weeks now. Today I went through my code one line at a time (pressing the F8 key) to see where it was getting hung-up and I think I discovered the source. My understanding of IF-THEN statements is that the condition is checked at each "IF" statement and only if the condition is True then the code after the "THEN" portion is run. When using IF-Elseif-Else, the code will check the ElseIf portion in a similar manner and if none are true, the ELSE section will run.
However, I have been noticing that with my particular code, in times I am certain that the ELSE portion should run it does not. I have checked this section of the code by commenting out all the related IF and ElseIf code above it and it works nicely.
So, my question is then this: With nested IF statements like I have, if some of the nested IFs are true but not all of them, will the ELSE statement become void at this point? Does Excel think "ok, something here in this first IF-Then nest is true, I'll keep checking this out, I can ignore the ElseIfs and ELSE statements after"
Thanks for bearing through this with me. The code I have shown could certainly be written better. Ultimately, I think the solution here for me is to not use nested if statements and compile the sub-level IF statements into one albeit VERY long IF statement where it checks if all of those things are true at once.
Any thoughts/recommendation/feedback would be greatly appreciated! P.S. I had to retype all the code below from a printed sheet on my home computer. The code i have on my actual work computer is all spelled correctly.
Range("I3").FormulaArray= "=INDEX(CombinedFillet,MATCH(1,(F3=StudMaterial)*(G3=BaseMaterial)*(H3=StudDiameter),0))"
If listboxStudMatl.ListIndex <> -1 And listboxStudDiamDecimal.ListIndex <> -1 And listboxBase.ListIndex <> -1 Then
If Not IsError (Application.Match(listboxBase.value, Sheets("Parts").Range("SupportRod"), 0)) Then
If listboxStudMatl.Value = "OSS/HSS" or listboxStudMatl.Value = "CRES" or listboxStudMatl.value = "HY-80" Then
If listboxStudDiamDecimal.Value = "0.375" Then
textboxJ1_3.Text = Cells.Range("M4").Text
MsgBox "Test 1" 'for debugging only
Elseif listboxStudDiamDecimal.value > 0.375 Then
MsgBox "Choose a different option"
Exit Sub
End If
End If
End If
ElseIf listboxParts.ListIndex <> -1 And listboxBase.ListIndex <> -1 Then
If Not IsError(Application.Match(listboxParts.Value, Sheets("Parts").Range("Stud375"), 0)) Then
textboxJ1_3.Text = Cells.Range("M4").Text
MsgBox "Test 2" 'for debugging only
End If
Else
MsgBox "Test 3" 'for debugging only
Range ("I3").Select
Selection.Copy
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues
textboxJ1_3 = Cells.Range("I4").Text
MsgBox "Test 4" ' for debugging only
End If
Aucun commentaire:
Enregistrer un commentaire