none
Data Validation macro issues RRS feed

  • Question

  • Hello-

    I am fairly new to writing in VBA, having some trouble writing specific validation steps. My goal is to validate the data received from clients in a template, which includes some simple steps (e.g., text length requirements, cell format) and some that are causing me some headaches.

    I'm living with some sub-optimal code for the time being, as I need the code to work correctly and I need to understand what the code says so that I can update it in the future far more than I need the code to be efficient (for example, I'm struggling to find a good way to determine the last record to check in each column, so I'm simply scanning from row 5 where the data should start to row 100000- I would otherwise need to determine the last row populated in *any* column, since the validation needs to run on incomplete records as well as complete ones, and some steps reference the data in other columns; the entire macro takes less than one minute to run, so I can live with the inefficiency knowing that it will never realistically skip a record).

    At the moment, I have full validation steps worked out for 15 columns of 18; the particular issue that I'm trying to work through is performing the following check steps on a column of data (column O):

    1. If the first cell in the row is populated, column O cannot be blank.
    2. The length of the string in column O cannot exceed 20 characters.
    3. The string in column O should appear in column A (SHOULD, not MUST)

    Since the client is responsible for correcting the data if it is invalid, I'm not trying to do any real data transformation, I'm just flagging the items for review in 2 colors (yellow if it violates a "should" rule and red if it violates a "must" rule).

    I had this section all worked out until I ran into a client that pasted values that included a standard Excel error code (#VALUE!) in this field, and my macro stopped working when it hit that line. I've played around with this validation step, and now I can't get it to work properly at all. Code for the problem column is below, the macro will run on all columns up to O but seems to break after starting the loop.

      'Reviewing Column O (Supervisor) for invalid entries
     Columns(16).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("P2").Formula = "=IFERROR(VLOOKUP(O2,$A$2:$A$100000,1,FALSE),0)"
     Range("P2:P100000").FillDown
      For Each cellentry In Range("O2:O100000")
       If cellentry.Value <> 0 Then
        If cellentry.Offset(, 1).Value = 0 Then
        cellentry.Interior.ColorIndex = 6
        End If
       End If
       If Len(cellentry.Value) > 20 Then
       cellentry.Interior.ColorIndex = 3
       End If
       If cellentry.Value = 0 Then
        If cellentry.Offset(, -14).Value <> 0 Then
        entry.Interior.ColorIndex = 3
        End If
       End If
      Next
     Columns(16).Delete

    Tuesday, March 8, 2016 8:48 PM

Answers

  • To deal with error values, do an explicit check before using the cell:

       Dim rngC As Range
        Dim cellEntry As Range

        'Reviewing Column O (Supervisor) for invalid entries
        Columns(16).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Set rngC = Range(Range("O2"), Cells(Rows.Count, "O").End(xlUp))

        rngC.Offset(0, 1).Formula = "=IFERROR(MATCH(O2,$A:$A,FALSE),0)"

        For Each cellEntry In rngC
            If Not IsError(cellEntry.Value) Then
                If cellEntry.Value <> "" Then
                    If cellEntry.Offset(0, 1).Value = 0 Then
                        cellEntry.Interior.ColorIndex = 6
                    End If
                End If
                If Len(cellEntry.Value) > 20 Then
                    cellEntry.Interior.ColorIndex = 3
                End If
                If cellEntry.Value = 0 Then
                    If cellEntry.Offset(, -14).Value <> 0 Then
                        cellEntry.Interior.ColorIndex = 3
                    End If
                End If
            End If
        Next cellEntry

        Columns(16).Delete

    PS: you should always use Option Explicit so that you will not get errors like this one - it is possible that this is what hurt you....

     If cellentry.Offset(, -14).Value <> 0 Then
        entry.Interior.ColorIndex = 3   'should have been cellentry

                    
    Tuesday, March 8, 2016 10:13 PM