Excel Coding

We have an Excel Add in that was created record orders for different clients. Herein lies the problem:
When you hit the (We’ll call it a label, because it’s not a button) – the code below executes. The problem is – if the entire range of C17-C1000 is populated by the “Retrieve Materials” label – it will not look for an error in a material number beyond C17. Case in point: if I change a number in C22 it will validate and save even if the number is not valid. It will automatically check for 10 or 13 digits without hitting Validate and Save though, just by tabbing out of the cell. Does this code look faulty in that regard – or is there something commented out that shouldn’t be?
Thanks for any input you can provide – there are many coders involved in this, and it is confusing at times.

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
    
'***JLH 11/18/2014: Validate "Sold To" numbers per Barry's request
  '***JLH 01/08/2015:  Expanding Order columns from 4 to 100
    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")
  '***JLH 01/08/2015 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    
    For intCol = LBound(arrColumn) To UBound(arrColumn)
        strCol = arrColumn(intCol)
        If Sheets("Template").Range("" & strCol & "3") <> "" Then   'If it's not blank, call the WebService
            Call fnGetSoldTo(Sheets("Template").Range("" & strCol & "3"))
            'If the Customer Number is not valid, throw a pop-up and exit the process
            If gblnWS_Status = False Then
                MsgBox "Sold-to is not valid: Customer web service returned no records."
                Exit Sub
            End If
        Else    'If it is blank, continue on
        End If
        
        '''''added the code to change entered text in to Upper Case
        Sheets("Template").Range("" & strCol & "4").Value = UCase(Sheets("Template").Range("" & strCol & "4"))
        Sheets("Template").Range("" & strCol & "9").Value = UCase(Sheets("Template").Range("" & strCol & "9"))
        Sheets("Template").Range("" & strCol & "10").Value = UCase(Sheets("Template").Range("" & strCol & "10"))
        Sheets("Template").Range("" & strCol & "11").Value = UCase(Sheets("Template").Range("" & strCol & "11"))
        Sheets("Template").Range("" & strCol & "12").Value = UCase(Sheets("Template").Range("" & strCol & "12"))
        Sheets("Template").Range("" & strCol & "106").Value = UCase(Sheets("Template").Range("" & strCol & "106"))
        
    Next intCol
''''''Changing Unit of Measure Cell values to Upper Case
Call Change_To_UpperCase
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'***JLH 11/18/2014 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    Dim i As Integer
    Dim Target As Range
    
    For i = 17 To 2977

'***JLH 01/20/2015: Material Number can be invalid, but Description does not blank out unless the user clicks out of the cell.
            'Edited code to check for length and numeric validity before checking that column D is blank.

' Rich 20141117
    '***JLH 01/20/2015
'    If Sheets("Template").Range("C" & i) <> "" And Sheets("Template").Range("D" & i) = "" Then
    If Sheets("Template").Range("C" & i) <> "" Then
    '***JLH 01/20/2015
' Rich 20141117
                     'Target = Sheets("Template").Range("C" & i).Value
                     'strMatNbr = Right("000000000000000000" & Replace(Sheets("Template").Range("C" & Mid(Target.Address, 4, 2)), "-", ""), i)
                     'strMatNbr = Right("000000000000000000" & Replace(Sheets("Template").Range("C" & i).Value, "-", ""), 18)
        'Deepak....
        strMatNbr = Sheets("Template").Range("C" & i).Value
        '***JLH 1/15/2015: Ensure the Material Number is numeric
        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
            'Exit Sub
        End If
        '***JLH 1/15/2015 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        If (Len(strMatNbr) = 10 Or Len(strMatNbr) = 13) Then
            '***JLH 01/20/2015: Moved this check down here...
                If Sheets("Template").Range("C" & i) <> "" And Sheets("Template").Range("D" & i) = "" Then
                '***JLH 01/20/2015 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                    Call fnGetMaterial("Special", Sheets("Template").Range("E3"), strMatNbr, i)
                End If
        
' Rich               'CALL THE WEB SERVICE BY MAT NBR TO RETRIEVE THE DESCRIPTION
'                        'If MATERIAL Is Not FOUND Then MARK Row
'                       'Sheets("Template").Range("B" & Mid(Target.Address, 4, 2)) = "X"
'                    'Else
' Rich               'End If

'                If Target.Column = 3 And Target.Row > 15 Then 'And Target.Column <> Empty Then
 '                   strMatNbr = Right("000000000000000000" & Replace(Sheets("Template").Range("C" & Mid(Target.Address, 4, 2)), "-", ""), 18)
'                    'CALL THE WEB SERVICE BY MAT NBR TO RETRIEVE THE DESCRIPTION
                    If fnGetMaterial("All", Sheets("Template").Range("E3"), strMatNbr, i) Then
                        MsgBox "Material Not Found", vbCritical
                    End If
                    
                '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:
' Rich 20141117
    End If
' Rich 20141117
    Next i

'***JLH 01/20/2015 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

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", vbCancelonly, "Alternate Order Entry"
        Sheets("Template").Range("C" & i).Value = ""
        Sheets("Template").Range("C" & i).Select
        Exit Sub
    End If

'***JLH 1/15/2015: Added If statement for gblnPassed
If gblnPassed = True Then
fnRequiredFields eReq, 4 'Updated on 11/06/2014 by Deepak
   ActiveWorkbook.Save 'Updated on 11/06/2014 by Deepak
End If
'***JLH 1/15/2015 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

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.

dawber39Database Analyst / Application DeveloperAuthor Commented:
I forgot to mention that C17 also Justifies right in the cell after validation - none of the other's do, they all stay justified left
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
This is probably the actual code behind the label Validate and save:

Private Sub Label1_Click()
 Dim i As Long, r As Range, coltoSearch As String

    coltoSearch = "C"

    For i = 17 To Range(coltoSearch & Rows.Count).End(xlUp).Row
        Set r = Range(coltoSearch & i)
        If Len(r.Value) = 0 Then
            r.Select
            Me.Label1.Visible = False
            Exit Sub
        End If
    Next i
    
End Sub

Open in new window

0
FarWestCommented:
I think this is due to treating some numbers as text, (justify issue) and maybe the root cause of the problem
try run this code before validating
Dim ic As Integer
For ic = 1 To ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Cells(ic, 3).Value = ActiveSheet.Cells(ic, 3).Value
Next

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dawber39Database Analyst / Application DeveloperAuthor Commented:
I tried it..... it still validates and saves as it did before, and now, still with the exception of C17, it wipes out the  material description for all numbers. I understand what you're getting at though. This is an abomination, but its my job to maintain it.
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
Same thing with before the clicking of the label Validate and save - only the Material descriptions stay in tact with the code there
0
FarWestCommented:
Sorry I did not get what you mean but the code is to fix error related to cell type settings
Anyhow if it is possible to post a sample of the sheet this will greatly help
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
I did just check the format of those cells though - and C17 is General, and some of the rest are text, at least the ones that I looked at.
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
I don't think you'll be able to call the webservices without the proper credentials behind you - but here is a sample
SampleWrkbook.xlsm
0
FarWestCommented:
very huge logic for a worksheet :)"
1-  C  cells after c17 has a data validation rule for the length and type, to apply same logic to C17 just copy c18 and paste it over C17
2- the sbValidateAndSave sub is the code that validate and save and not lable1_click which is not executed at all
3- there is performance issue because you check all C column (   For i = 17 To 2977) and I don't think that every order need such number of items, so you can make a cell for user to specify number of items and only work for that range
(by hiding all additional rows) and only work with those rows

for point 1 I could not validate the data validation rule because the workbook is a password protected

good luck
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
AlternateOrderEntry should be the password or 781612aa
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
I also noticed that if a material number is entered into a cell like C25 and the above cells are not populated - it will find it as an invalid number - its really wierd. I would have built this in Access or something - but thats not what they want..... so I gotta figure this out so that it will find an invalid number anywhere in C from 17 on. I noticed you can change C17 a dozen times - and it will come back as invalid every time - but nothing below it. It does have a validation check for digit count. I think you might be right in this justification issue - I noticed at the top of fnWorksheet_Change the column is declared as a string but the start row of 17 is declared as Long - C17 is formatted as General - with the rest formatted as text.... so damn confucing.
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
Even if the start material number is in C22 - it will be caught as invalid - but nothing below it will. Its like it stops after the first one - no matter where it is - and as long as the cells above it are not populated
0
FarWestCommented:
please note validation stops After first validation mismatch, did it happen that C22 is valid and C23 is invalid and not caught?
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
C22 was invalid, and so was C23 but it wasn't caught. I am going to try it again - with a valid number at the C22 and an invalid in C23 and see if it catches it
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
What I got was "There are no Material Numbers Listed - please hit the retrieve materials button" But it effectively listed the proper description next to the valid material number - and didn't catch the invalid. This just keeps getting more and more fun.... NOT. I really appreciate the effort you are putting into this.
0
dawber39Database Analyst / Application DeveloperAuthor Commented:
That is the way it happens - after the first validation is caught - it goes no further. so if the first Material number has a valid number - then the rest pass, and they shouldnt
0
FarWestCommented:
Yes. And this is what I told before. Since the logic is to move to that cell to allow user to correct the problem. So no means to keep showing errors
0

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
dawber39Database Analyst / Application DeveloperAuthor Commented:
When you said copy cell and paste it over, are you referring to the validation that C17 gets as opposed to the others when the material list is populated? if so - I should copy C17 and paste it over 18 - or the other way around?
0
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.