Excel VBA

Is there anything I can add to this to insure that the process checks the remaining cells in the range for a valid Material Number? It just stops after validating the first row C17. The first Cell in the range was formatted as General - and the rest as text - I've since changed them all to General. I've tried writing functions to call - adding to the fnWorkSheet_Change, and all to no avail. There is no wrong answer - I am willing to try anything at this point. The array expands the WorkBook to 100 Available slots to place an order.

Thanks for your help

Public Sub sbValidateAndSave()
On Error GoTo MBR_ERROR
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strMatNbr As String


    gblnPassed = True
    
    strCriteria1 = nz(Trim(Range("E3").Value), "")
    strCriteria2 = nz(Trim(Range("E4").Value), "")
    
    If strCriteria1 = "" Or strCriteria2 = "" Then
        MsgBox "You must select a Sales Org and Doc Type"
        Sheets("Template").Select
        If strCriteria1 = "" Then
            Range("E3").Select
            Exit Sub
        ElseIf strCriteria2 = "" Then
            Range("E4").Select
            Exit Sub
        End If
      
    End If
    
    Dim arrColumn As Variant
    Dim intCol As Integer
    Dim strCol As String
    
    arrColumn = Array("I", "L", "O", "R", "U", "X", "AA", "AD", "AG", "AJ", "AM", "AP", "AS", "AV", "AY", "BB", "BE", "BH", "BK", "BN", "BQ", "BT", "BW", "BZ", "CC", "CF", "CI", "CL", "CO", "CR", "CU", "CX", "DA", "DD", "DG", "DJ", "DM", "DP", "DS", "DV", "DY", "EB", "EE", "EH", "EK", "EN", "EQ", "ET", "EW", "EZ", "FC", "FF", "FI", "FL", "FO", "FR", "FU", "FX", "GA", "GD", "GG", "GJ", "GM", "GP", "GS", "GV", "GY", "HB", "HE", "HH", "HK", "HN", "HQ", "HT", "HW", "HZ", "IC", "IF", "II", "IL", "IO", "IR", "IU", "IX", "JA", "JD", "JG", "JJ", "JM", "JP", "JS", "JV", "JY", "KB", "KE", "KH", "KK", "KN", "KQ", "KT")
    
Call Change_To_UpperCase
    Dim i As Integer
    Dim Target As Range
    
    For i = 17 To 1000


    If Sheets("Template").Range("C" & i) <> "" Then
    
        strMatNbr = Sheets("Template").Range("C" & i).Value
      
        If IsNumeric(strMatNbr) = False Then
            MsgBox ("Please enter a valid 10 digit or 13 digit Material number")
            Sheets("Template").Range("C" & i).Value = ""
            Sheets("Template").Range("C" & i).Select
            gblnPassed = False
            GoTo Continue
                  End If
      
        If (Len(strMatNbr) = 10 Or Len(strMatNbr) = 13) Then
                If Sheets("Template").Range("C" & i) <> "" And Sheets("Template").Range("D" & i) = "" Then
                    Call fnGetMaterial("Special", Sheets("Template").Range("E3"), strMatNbr, i)
                End If
                            If fnGetMaterial("All", Sheets("Template").Range("E3"), strMatNbr, i) Then
                        MsgBox "Material Not Found", vbCritical
                    End If
         Else
                If Len(strMatNbr) <> 0 Then
                    MsgBox ("Please enter a valid 10 digit or 13 digit Material number")
                    Sheets("Template").Range("C" & i).Value = ""
                    Sheets("Template").Range("C" & i).Select
                    gblnPassed = False
                Else
                    Exit For
                End If
         End If
Continue:
    End If
    Next i

MBR_ERROR:

    If Err.Number = 5 Then
        fnRequiredFields eReq, 4
        ActiveWorkbook.Save
    ElseIf Err.Number = 13 Then
        MsgBox Sheets("Template").Range("C" & i).Value & " is invalid Material number.Please enter valid number"
        Sheets("Template").Range("C" & i).Value = ""
        Sheets("Template").Range("C" & i).Select
        Exit Sub
    End If
If gblnPassed = True Then
   ActiveWorkbook.Save
End If
End Sub

Open in new window

dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I would try commenting out line 65 and see if that's better.
dawber39Database Analyst / Application DeveloperAuthor Commented:
No - that didn't matter - same thing. Here's whats going on - if the range is populated, and a material number is changed in say C:25 to an invalid number - it will validate what is in C17 - stop there, validate and save the entire list. This C and i thing obviously isn't working - so I need to do something to this.  - I appreciate your help
NorieAnalyst Assistant Commented:
How are you executing this code?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

David Johnson, CD, MVPOwnerCommented:
commenting your code would be a good idea.. you don't reference the array in your displayed code.
   If (Len(strMatNbr) = 10 Or Len(strMatNbr) = 13) Then
                If Sheets("Template").Range("C" & i) <> "" And Sheets("Template").Range("D" & i) = "" Then
                    Call fnGetMaterial("Special", Sheets("Template").Range("E3"), strMatNbr, i)
                End If
        If fnGetMaterial("All", Sheets("Template").Range("E3"), strMatNbr, i) Then
                        MsgBox "Material Not Found", vbCritical
        End If
		Else If Len(strMatNbr) <> 0 Then
                    MsgBox ("Please enter a valid 10 digit or 13 digit Material number")
                    Sheets("Template").Range("C" & i).Value = ""
                    Sheets("Template").Range("C" & i).Select
                    gblnPassed = False

Open in new window


Guessing you are checking for a proper Material Code .. here I'd use the for/next loop and if
the value in your sheet matches a template Material code then break out of the loop
I use break rather than goto then compare the value of 'i' and if it is < the maximum value of i then you've got a proper Material code otherwise the Material code is wrong. Do your check for Material # length first before you search for the value from Template.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can you add a new line 71

Msgbox i

And let us know the result.

Another good way of seeing what is happening is to insert after row 37

If I=23 then
   If 1=1 then
   End if
End if

And add a breakpoint to the second line.

When it reaches that breakpoint, repeatedly press F8 to see how the code works for C23.
dawber39Database Analyst / Application DeveloperAuthor Commented:
I will try that as soon as I get in
dawber39Database Analyst / Application DeveloperAuthor Commented:
I found the problem within that huge abomination of code. The code actually checks each iteration of the material number as it progresses, and I thought it was just stopping t the first one. When each number is checked, it returns with either the number and appropriate info for that number, and if it is valid it returns a [ ] symbol. This is the key - because there is an if statement that says if it is returned like that, then the cell = " " then gives the testing function a PASSED - which in turn directs the code progression to skip over the MsgBox  "9988898897 is an invalid Material number". So - I removed the line giving it a pass - created another variable with an if statement and placed it into the existing if statement with the MsgBox, and added and end function thereafter. - Works perfectly - thank you for all your help. I will have to post this comment in several other places. Thanks again guys - you got me looking in the right places
dawber39Database Analyst / Application DeveloperAuthor Commented:
Thank you for hel;p peop[le - always a pleasure coming here for guidance
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.