Need Elseif to change with each row

[andrewb90]

Hello All,
I have a code validation page setup. But on my sheet InGen I have a list of validation codes and their corresponding expiration dates. Now I could just do an elseif statement that matches the original if statement over and over again, but I was wondering if there is a far more elegant way of doing this?

Basically the codes run from row 4781 through 4791. So the first IF would be 4781, then the ElseIf would be 4782, the next ElseIf would be 4783, etc.

Is there a simple way of doing this?

Private Sub validate()If Range("D6").Value = Sheets("InGen").Range("XAB4781") Then
    If Now() > CDate(4 / 14 / 1912) And Now() < CDate(Sheets("InGen").Range("XAC4781")) Then
    MsgBox "Your code has been accepted." & vbNewLine & vbNewLine & "Your Software is valid until " & Sheets("InGen").Range("XAC4781"), vbInformation, "The Code Validation Robot Says:"
    Else:
    MsgBox "Your code is not valid." & vbNewLine & "This code expired on " & Sheets("InGen").Range("XAC4780"), vbInformation, "The Code Validation Robot Says:"
    End If


Else:
    MsgBox "Your code is invalid." & vbNewLine & "Please check the code and try again." & vbNewLine & "Contact support with any issues.", vbInformation, "The Code Validation Robot Says:"
End If
    
    
End Sub

 

[dmt32]

Hi,
as suggestion, maybe a For Next Loop will do what you want?

something like following:

Private Sub Validate()
    Dim ValidateCode As Integer
    Dim wsInGen As Worksheet
    Dim Title As String
    
    Title = "The Code Validation Robot Says:"
    
    Set wsInGen = ThisWorkbook.Worksheets("InGen")
    
    For ValidateCode = 4781 To 4791
        
        If Range("D6").Value = wsInGen.Range("XAB" & ValidateCode) Then
            
            If Now() > CDate(4 / 14 / 1912) And Now() < CDate(wsInGen.Range("XAC" & ValidateCode)) Then
                
                MsgBox "Your code has been accepted." & vbNewLine & vbNewLine & _
                "Your Software is valid until " & wsInGen.Range("XAC" & ValidateCode), vbInformation, Title
            Else
                
                MsgBox "Your code is not valid." & vbNewLine & _
                "This code expired on " & wsInGen.Range("XAC" & ValidateCode - 1), vbInformation, Title
                
            End If
            
            
        Else
            
            MsgBox "Your code is invalid." & vbNewLine & "Please check the code and try again." & vbNewLine & _
            "Contact support with any issues.", vbInformation, Title
            
        End If
        
        Next ValidateCode
        
End Sub

example is not intended as a complete or working solution but just to give you some ideas.
Note the range I have highlighted in RED - which is unqualified - To ensure your code performs as intended, you should qualify it to required worksheet.


Hope Helpful

Dave

 

[andrewb90]

That's getting close. I'm tweaking it a bit. Adding Exit sub after the msgbox seemed to fix getting an invalid msgbox repeatedly after the first code was correct, however if I chose a code that was halfway down the list, I would get several invalid messages then the code would get accepted, then exit.
I feel like if I put an elseif in the loop, then I would only get one response instead of a msgbox for every row.

Any ideas?

 

[dmt32]

It was just a suggestion for you to play with to see if could adapt.

Without see copy of your workbook can only guess but perhaps just test for a valid code in Loop & report status according to what is found.

Example:

Private Sub Validate()
    Dim ValidateCode As Integer
    Dim wsInGen As Worksheet
    Dim Title As String
    
    Title = "The Code Validation Robot Says:"
    
    Set wsInGen = ThisWorkbook.Worksheets("InGen")
    
    For ValidateCode = 4781 To 4791
        
        If Range("D6").Value = wsInGen.Range("XAB" & ValidateCode) Then
            
            If Now() > CDate(4 / 14 / 1912) And Now() < CDate(wsInGen.Range("XAC" & ValidateCode)) Then
                
                MsgBox "Your code has been accepted." & vbNewLine & vbNewLine & _
                "Your Software is valid until " & wsInGen.Range("XAC" & ValidateCode), vbInformation, Title
            Exit Sub
            
        End If
        
        Next ValidateCode
        
         
            MsgBox "Your code is invalid." & vbNewLine & "Please check the code and try again." & vbNewLine & _
            "Contact support with any issues.", vbInformation, Title
        
End Sub

If you are just checking a number against a range of numbers you may not need to perform a loop test but as said, without seeing copy of your workbook, it's just guessing.

Anyway - I am about to head out of the door for weekend away - hope suggestions are of some help.

Dave