User Error Logs / Matrix / Array / ? - VBA Excel

[remeng]

Hi All;

I don't know where to start on this project and was hoping for some guidance. I've included a photo to help explain what I am trying to do.

I have a list of information on a "data" sheet. The headers are also listed on an "error rules" sheet (see picture). I have some engineers that need to be able to check their data based on conditions specified on the "error rules" sheet. Each rule has its own row. The reason for the "error rules" sheet is so that the engineers can add rules as production needs change. Currently they are using very complex macros that are difficult to edit (prior to my time). We are talking about 20+ nested IF statements in the formula bar. Messy to say the least.

What I would like to do is have the engineer be able to add rules to the "error rules" sheet as either an OR statement or an AND statement.

The engineer would select AND or OR, the macro looks at all the cells in the row and evaluates each one against the data row in the "data" sheet. The headers in each sheet must match since I am concerned that someone will add columns to the "error rules" sheet that aren't matched to the "data" sheet table layout exactly. I'd like this to be as robust as reasonably possible.

I am going to have them use 1 line per rule. Most of the statements are going to be AND statements. Only a few will be OR statements. The engineer will not be able to use an AND an OR statement in the same row to elevate confusion.

I will have a LOOP so that each row on the "data" sheet will be checked against all of the rules on the "error rules" sheet.

Example 1:​

DEPT = 100 AND Part_Description = Fred​

The macro goes to the first row and checks to see if Dept = 100 and Part_Description = Fred​

IF conditions = TRUE then insert Error Message "Test 1 - Both Met" into a specified location​

Example 2:​

#Model_Number = ABC​

Part_Level_Number = 2​

The macro goes to the first row and checks to see if #Model_Number = ABC or Part_Level_Number=2​

IF conditions = TRUE then insert Error Message "Test 2 - 1 or Both Met"​

Example 3:​

Error Code 1 and 2 are met. Both Error Message need to be entered into a specified cell. There might be 10+ errors that need to be added to the same cell​

Thought process:

1) Count rows in "data" sheet to determine how many loops to be performed on rule checking and set
2) Count rows in "error rules" sheet to determine the total number of rules that need to be checked
3) Start with Row 1 of "data"
4) check the "data" row for the AND / OR rules line by line on the "error rules" sheet
5) return all record errors to a cell / note on the "data" sheet
6) LOOP process to all records have been validated

What is the best way to start? Matrix, Array, something else? I KNOW that this is going to be the most complex VBA project I have ever undertaken.

Thanks,

Mike

 

[remeng]

Hi Combo,

I've attached the test document if you'd like to try it with the actual file. I'll look at your code with the file in the mean time.

Thanks,

Mike

 

[combo]

Without digging too much in your code, I run error_check in Test_Sandbox module, step by step for i=2. The executed part:

and the result in the 'data' sheet, active cell in row 2:

The same if active cell is in different row:

Probably any of the results is not what you planned. Again be careful with @-references in VBA, they can relate to active cell. It may be simpler to loop 'data' table row by row.

combo

 

[remeng]

Hi Combo,

Thanks for looking at this. The code is actually located in the Workbook code at the bottom and not the sandbox. Sorry for the confusion. I am still working on row by row just as a backup.

If you have a chance, can you try the other code?

For record 1 (row 2), the result should be:

Bare Core must be 5 x 8 and not 8 x 5
Part Exceeds Largest Possible Core Dimension

Best regards,

Mike

 

[combo]

The code depends on active cell. ActiveWorkbook.Sheets("data").[data[@[Error Message]]].Value picks data from the active cell row. ActiveWorkbook.Sheets("data").[data[Error Formula]].Value (variant) picks data from the whole column, into variant array. This generates type mismatch error in
[tt]combined_error_msg = current_error_msg & Chr(10) & new_error_msg[/tt].

https://www.tek-tips.com/viewthread.cfm?qid=1809088

Without '@' you get data column range and assign to it variant array in VBA, you need to process each item. Without another loop you won't be able to build 'Error Message' column

combo

 

[remeng]

Hi Comb,

Here is the working code. It isn't as clean as the previous one, but it works. Is there a way to reduce the lines?

Also, I am having a problem with CountA for column A (non blanks) so I can return a msgbox to the user stating how many records have errors. I am getting a generic error. I've tried the standard range as A2:A66 as a test without any luck. Any ideas why?

Also, is there a way to count the number of - in a column? I have added the minus sign to the beginning of each text rule. Since each rule is added following the previous, I'd be able to return the number of errors.

Sub error_check_row_by_row()

'This macro counts the number of records in the data Model Number column and runs the script line by line

'resets error messages and error formula columns to blank

ActiveWorkbook.Sheets("data").[data[Error Formula]].Formula = ""
ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = ""

'Counts the total number of rules and the total number of records

total_rules = ActiveWorkbook.Sheets("error rules").Cells(Cells.Rows.Count, "B").End(xlUp).Row
total_records = ActiveWorkbook.Sheets("data").Cells(Cells.Rows.Count, "C").End(xlUp).Row

rules = 2
records = 2

'resets error messages and error formula columns to blank

ActiveWorkbook.Sheets("data").[data[Error Formula]].Formula = ""
ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = ""

'determines the columns associated with the Materials Header, #Model_Number Header, Error Message Header, and the Error Formula Header

materials_column = ActiveWorkbook.Sheets("data").[data[Material]].Column
models_description_column = ActiveWorkbook.Sheets("data").[data[Model_Description]].Column
error_formulas_column = ActiveWorkbook.Sheets("data").[data[Error Formula]].Column
error_message_column = ActiveWorkbook.Sheets("data").[data[Error Message]].Column


Do While rules <= total_rules 'Performs loop so that each rules is checked one by one until all of the rules have been run


    ActiveWorkbook.Sheets("data").[data[Error Formula]].Formula = ActiveWorkbook.Sheets("error rules").Cells(rules, error_formulas_column).Value 'places the rule formula into the Error Formula column in the "data" sheet "data" table
    
        
        Do While records <= total_records 'Performs loop so that each record is checked one by one until the individual rule has been run
        
        
        
        If ActiveWorkbook.Sheets("data").Cells(records, materials_column).Value <> "" Then 'Checks only records that have a defined Material.  Any records without a Material defined will be ignored
    
                If ActiveWorkbook.Sheets("data").Cells(records, error_formulas_column).Value = "" Then 'If a record doesn't have an Error Message returned in the Error Formula cell, no additional actions will be taken and the next record will be processed
                
                Else
                
                    If ActiveWorkbook.Sheets("data").Cells(records, error_message_column).Value = "" Then 'Determines if an Error Message already exists and if not, will copy and paste the new error into the Error Message cell
                    
                        ActiveWorkbook.Sheets("data").Cells(records, error_message_column).Value = ActiveWorkbook.Sheets("data").Cells(records, error_formulas_column).Value
                        
                    Else 'If an Error Message already exists for the record, it will add the new record after the existing record into the Error Message cell
                    
                        ActiveWorkbook.Sheets("data").Cells(records, error_message_column).Value = ActiveWorkbook.Sheets("data").Cells(records, error_message_column).Value & Chr(10) & ActiveWorkbook.Sheets("data").Cells(records, error_formulas_column).Value
                        
                    End If
                    
                End If
    
        Else
        
        End If
    
            records = records + 1
    
        Loop
    
    
    rules = rules + 1
    records = 2

Loop

'Reports the number of records with errors

error_count = CountIf(ActiveWorkbook.Sheets("data").[data[Error Message]], "")


End Sub

Thanks!

Mike

 

[combo]

I will check it tomorrow. As your code is in the workbook you process, ThisWorkbook instead of ActiveWorkbook is more secure, it always refer to the workbook with calling it code. I think that using structured table references more widely could shorten the code, variant arrays picked from columns will speed up processing.

How do you apply CountA for column A, what do you mean by generic error? Counting the number of minus sign - by code or worksheet formula? in the beginning or whole cell? numbers (if so, count negative numbers) or text?

combo

 

[combo]

The code below should be faster (no error message processing in the worksheet) and returns no. of errors. In 'error rules' sheet i added table ErrorRules, used later in the code. Some comments are yours, other are modified or added.
ThisWorkbook object call should be universal, however I tested the code in standard module. You use ThisWorkbook workbook's module for all the code, I would rather split it between standard smaller, functional modules, easier to handle, also, it is rather a place for workbook events and variables:

Sub error_check_row_by_row2()
Dim iRule As Long, iRecord As Long
Dim error_msg As Variant

' This macro counts the number of records in the data Model Number column and runs the script line by line

' save several 'ThisWorkbook' calls in procedure
With ThisWorkbook
    ' resets error messages and error formula columns to blank
    .Worksheets("data").[data[Error Formula]].ClearContents
    .Worksheets("data").[data[Error Message]].ClearContents
    
    ' Counts the total number of rules and the total number of records
    total_rules = .Worksheets("error rules").[ErrorRules].Rows.Count
    total_records = .Worksheets("data").[Data].Rows.Count
    
    ' Picks current Error Message. Initially empty array, will be filled when processing rules and records, returned back after processing
    error_msg = .Worksheets("data").[data[[Error Message]]]
    
    ' Performs loop so that each rules is checked one by one until all of the rules have been run
    For iRule = 1 To total_rules
        ' places the rule formula into the Error Formula column in the "data" sheet "data" table
        .Worksheets("data").[data[Error Formula]].Formula = .Worksheets("error rules").[ErrorRules[Formula]].Cells(iRule).Value
        
         ' Performs loop so that each record is checked one by one until the individual rule has been run
         For iRecord = 1 To total_records
            ' Checks only records that have a defined Material. Any records without a Material defined will be ignored
            If .Worksheets("data").[data[[Material]]].Cells(iRecord) <> "" Then
                If .Worksheets("data").[data[Error Formula]].Cells(iRecord).Value <> "" Then
                    ' If a record doesn't have an Error Message returned in the Error Formula cell, no additional actions will be taken and the next record will be processed
                    If IsEmpty(error_msg(iRecord, 1)) Then
                        ' Check if an Error Message is in related err_msg item, if not, copy new error here
                        error_msg(iRecord, 1) = .Worksheets("data").[data[Error Formula]].Cells(iRecord).Value
                    Else
                         ' If an Error Message already exists for the record, it will add the new record after the existing data in error_msg item
                        error_msg(iRecord, 1) = error_msg(iRecord, 1) & Chr(10) & .Worksheets("data").[data[Error Formula]].Cells(iRecord).Value
                    End If
                End If
            End If
        Next iRecord
    Next iRule
    
    ' Fill Error Message column
    .Worksheets("data").[data[Error Message]] = error_msg

    ' clear Error Formula column with last rule
    .Worksheets("data").[data[Error Formula]].ClearContents

    ' Reports the number of records with errors
    error_count = total_records - Application.WorksheetFunction.CountBlank(.Worksheets("data").[data[Error Message]])
    MsgBox error_count
End With ' ThisWorkbook
End Sub

combo