Checking for unwanted data from Multiple Columns and generate error log using Macro

[samantha87]

Hi,

I have a set of data, which I need to check for data within specified columns say Col. A, C, L. They need to fulfill the following criteria:

If (cell value <> "100", "80", "NA"), then generate a log in a new sheet returning the Column header of where the error occurs, if all OK, proceed to run another macro.

I'm trying to use macro to shorten the process.

Any idea?

 

[WarPigl3t]

Sub mainMacro()
     wereThereErrors = myMacro()
     If wereThereErrors = False Then
          yourMacro()  'You said that if there are no errors then it should run another macro so yourMacro goes here.
     End If
End Sub

Function myMacro()
     dataWS = ActiveSheet.Name
     Dim columnsToReview("A", "C", "L") As Variant
     Dim acceptableCellValues("100", "80", "N/A") As Variant
     firstRow = 2
     Dim errorList() As Array
     e = 0

     For Each c in columnsToReview
          r = firstRow
          lastRow = Sheets(dataWS).Range(c & Rows.Count).End(xlUp).Row
          Do Until r > lastRow
               myValue = Sheets(dataWS).Range(c & r).Value
               returnError = True
               For Each a in acceptableCellValues
                    If myValue = a Then
                         returnError = False
                    End If
               Next a
               If returnError = True Then
                    ReDim Preserve errorList(e)
                    errorList(e) = c & r
                    e = e + 1
               End If
               r = r + 1
          Loop
     Next c
     If errorList(0) <> "" Then
          Sheets.Add
          errorSheet = ActiveSheet.Name
          Sheets(errorSheet).Range("A1").Value = "Error List"
          o = 2
          For Each err in errorList
               Sheets(errorSheet).Range("A" & o).Value = err
               o = o + 1
          Next err
          myMacro = True
     Else
          myMacro = False
     End If
End Function

 

[samantha87]

Hi WarPigl3t,

Thanks for your reply.

The code:

Dim errorList() As Array

is appearing red in my vba. I'm using excel 2007, if this affects?

 

[WarPigl3t]

Maybe change Array to Variant