×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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


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

(OP)
Update:

After talking with the engineers, they'd like to have formulas per rule line instead so that they can have more complex rules. This actually will be easier I think for the programing too.

If I set the column header cells range name to equal the column header and use those in the formulas, will that resolve the issue if someone changes the sequencing of the columns too?

is this now a simple loop statement were I loop the cell formula and do a return of the error and highlight the effected cells?

Thanks,

Mike

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

Mike,

Keep your heading values consistent. The order should be irrelevant.

This is not a particularly complex project. One step at a time.

AND--Are usually all in one row. If any test value fails, the AND is FALSE.
OR--Are usually in a separate row.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
Hi,

So it seems like I will need to use the FIND function in the data tab to locate the specific column for the IF / THEN statement to run on. I don't have a problem with that. What I'd like to understand is, if I have a custom formula on the error rules sheet, what is the best way to apply it so that the formula loops on the column?

I'm stuck on how to take a formula that isn't defined by any range in one sheet and program it to assign itself to the column in another sheet.

So I guess it is something like if a formula is based on the "data" sheet column headers, it will check the associated columns based on their headers and perform a loop check for all the rows.

Example:

Column A = Material
Column B = Color
Column C = Dept

The general formulas are located on the Error Rules sheet

Formula outline

if Material > 0 then color needs to be > NULL and Dept = 10 else return error in A some row number #

Is this even possible?

Thanks,

Mike

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

If you are looking for an outcome that looks something like this:

   A        B     C       D
Material Color	Dept	Error?
1        Blue	10	OK
2		10	Error
0        Black	9	Error
5        Red	11	Error
 
"if Material > 0 then color needs to be > NULL and Dept = 10 else return error in A some row number #"
Then, I 'translated' your 'Formula outline' to this (in cell D2 and down):
=IF(AND(AND(A2>0, LEN(B2)>0), C2=10), "OK", "Error")

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

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

(OP)
Hi Andy,

It is sort of what I am looking at. I think it is a little hard to explain. Let me try again though.

Here is the code I started to work on. It pulls the header name from the "definitions" sheet and finds it in the "test_data" sheet.

After the column number is returned, the formula will be able to have a start point. Since the formula doesn't know where in "test_data" it is initially being applied to, how do I set the formula in the "definitions" sheet to handle the columns being variable?

The columns could be like:

Material | Color | Dept

or

Color | Material | Dept

or

Dept | Material | Color

etc.

so in the cell where the rule is kept, it would be something like:

= if( Material > 0 and color > NULL and Dept = 10, , return error in A some row number #)


CODE -->

Sub find_header()

'********  WORK IN PROGRESS *******

'macro finds header from definition and returns the column number / id


condition_terms = 2

records = ActiveWorkbook.Sheets("definitions").Cells(Cells.Rows.Count, "A").End(xlUp).Row

Do While condition_terms <= records

 
    search_term = Sheets("definitions").Cells(condition_terms, 1).Value
    
    
    
    Sheets("test_data").Select
    Cells.Find(What:=search_term, After:=ActiveCell, LookIn:= _
        xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        
subject_column = ActiveCell.Column
info = ActiveCell.Value
        
        
condition_terms = condition_terms + 1
        
Loop


End Sub 

I hope this helps explain it a little better.

Mike

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

(OP)
Ok. Well I have made a significant amount of progress on this and have got the values to return correctly based on what the column and row are defined as using NAME for the cell name. I think I only need one item left to be tackled and I don't know how to go about it.

since the formula will be based off of a series of values, is there a way to call a macro from inside a IF formula located in a cell? I also do not want it to be run until a button is pressed. Is this possible and how can it be done without turning off Auto Calculate?

so here is what I have so far:

The cell has a NAME defined



Here is the code to set the NAME for each cell in the table where the data is stored

CODE -->

Sub name_set() 'working NAME set based on cell value

Call DeleteNames

i = 3 ' "definitions" Skip the "Error Message" and "#Model_Number" columns due to "Error Message" not having a definitions and "#Model_Number" being an invalid cell NAME.  Sets values in the "data" sheet
d = 3 ' starting location for the definitions.  Starts after "#Model_Number" row due to "#Model_Number" being an invalid cell NAME

total_def = ActiveWorkbook.Sheets("definitions").Cells(Cells.Rows.Count, "A").End(xlUp).Row 'counts the number of column definitions on the "definitions" sheet


Do While i <= total_def 'sets the loop to cycle through the "definitions" until the number of "definitions" has been reached


    Dim location As Range 'value used to sheet initial cell where the NAME originates from
    
    Set location = ActiveWorkbook.Sheets("data").Cells(1, i) 'Sets the target for the actual cell that you want to be NAMEd


    location.Name = ActiveWorkbook.Sheets("definitions").Cells(d, 1).Value 'Vale where the name will be generated from

i = i + 1
d = d + 1

Loop

End Sub 

Here is the code to pull the value from the table based on the column NAME value.

CODE -->

Sub value_return_dept() 'returns the value in the "data" table under a specified column


' "dept" will be called out in a formula within a cell's IF statement

bob = Range("dept").Row 'returns the row address for the "dept" column
sam = Range("dept").Column 'returns the column address for the "dept" column

Do While bob <= 100 ' 100 will be defined by the total number of rows in the data table.  Code will be changed in the future.  100 is a place holder for testing.

fred = Cells(bob + 1, sam).Value ' returns the value inside the cell

bob = bob + 1 ' selects the next row

Loop

End Sub 

what I'd like to do is have a cell call the value_return_dept macro and check the conditions in an IF formula defined in a cell where the engineer can enter the NAME and set the conditions for the IF statement / formula. Again, this is to be triggered by a button and not when the cell is exited.

Thanks for the help and support.

Mike

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

(OP)
Hi,

I've come a long way on this problem. I am now having an error in the current_error_msg adding the new_new_error_msg when the current_error_msg is not blank. My code process the new formula correctly and provides the new error message correctly, but will not add it to the error message column without overwriting the current value. I know the issue is in the IF current_error_msg <> "" of the code. How can I fix it?

Thanks for the help!

Mike

CODE -->

Sub error_check() ' evaluates rules and adds error log to record

'Application.ScreenUpdating = False

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


r = 2

total_rules = ActiveWorkbook.Sheets("error rules").Cells(Cells.Rows.Count, "C").End(xlUp).Row 'Column "C" / #Model_Number is selected to count due to Column "Error Messge" and Column "Error Formula" being added

material_value = IsEmpty(ActiveWorkbook.Sheets("data").[data[@[Material]]].Value)

Do While r <= total_rules

    If material_value = False Then
    
    Else
    
        ActiveWorkbook.Sheets("data").[data[Error Formula]].Formula = ActiveWorkbook.Sheets("error rules").Cells(r, "B").Value
        
        current_error_msg = ActiveWorkbook.Sheets("data").[data[@[Error Message]]].Value
    
        new_error_msg = ActiveWorkbook.Sheets("data").[data[Error Formula]].Value
 

 
    
    End If
    
    
    If current_error_msg <> "" Then
        
        
                combined_error_msg = current_error_msg & Chr(10) & new_error_msg
                
                ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = combined_error_msg
            
    Else
            
                ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = new_error_msg
        
        
    End If

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

r = r + 1

Loop



'Application.ScreenUpdating = True

End Sub 

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

Quote (Mike)

will not add it to the error message column without overwriting the current value

current value = current value & new error message
or in other words:
error message column = error message column & new error message ponder

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

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

(OP)
Hi Andy,

The error formula would be an IF statement that returned an error message in the "Error Formula" column (inside a table called "data") from another sheet called "error rules". This section of the code works.

The code then takes the value from the "Error Formula" cell and pastes it into the "Error Message" cell. This section of the code also works.

Once the script goes from r = 2 to r = 3, the next rule located in the "error rules" sheet is run. This works correctly.

What is broken is that the section of the code where it copies the current error in the "Error Message" cell with the new error message generated in the "Error Formula" cell. It appears that the script isn't able to determine that there is a value already located in the "Error Message" cell which causes it to go to the ELSE statement instead of the combine_error_msg formula.

CODE

If current_error_msg <> "" Then
        
        
                combined_error_msg = current_error_msg & Chr(10) & new_error_msg
                
                ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = combined_error_msg
            
    Else
            
                ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = new_error_msg
        
        
    End If 

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

If current_error_msg is a String, or something that you could display, I would try:

CODE

Debug.Print current_error_msg
If current_error_msg <> "" Then
    combined_error_msg = current_error_msg & Chr(10) & new_error_msg
    ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = combined_error_msg
Else
    ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = new_error_msg
End If 

and stop and SEE what's in there.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

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

(OP)
Andy,

As I expected based on the results on the second loop current_error_msg = "" even though there are values in the "Error Message" column in the formatted table.

It's weird since the original code works if there is no other code except this:

CODE

current_error_msg = ActiveWorkbook.Sheets("data").[data[@[Error Message]]].Value
new_error_msg = ActiveWorkbook.Sheets("data").[data[@[Error Formula]]].Value

combined_error_msg = current_error_msg & Chr(10) & new_error_msg

ActiveWorkbook.Sheets("data").[data[@[Error Message]]].Value = combined_error_msg 

The current embedded code returns the proper values, just won't transfer them to the Error Message cell correctly.

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

Looks like you are wiping out current_error_msg somewhere...

Would that help?

CODE

If ActiveWorkbook.Sheets("data").[data[@[Error Message]]].Value <> "" Then
    combined_error_msg = current_error_msg & Chr(10) & new_error_msg
    ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = combined_error_msg
Else
    ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = new_error_msg
End If 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

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

(OP)
Hi Andy,

I tried your code and I get the exact same thing. What are the different terms for "" or having no value in the cell? Maybe it is showing as blank but it is something else? Also, could cell format have something to do with it? For instance text versus general format?

Thanks,

Mike

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

Unless you have some unprintable 'stuff' in a cell, like Chr(10), Chr(13), Tab character and such, "" is usually sufficient to check if there is anything in a cell.
You may use Len(Trim(current_error_msg)) but my guess is you will have the same outcome.
So it looks to me there isn't anything in the cell or variable, no matter how you check it.

Quote (remeng)

could cell format have something to do with it?
No. Format does not change anything in the cell itself. Format just changes the display of what's in it.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

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

If you use "@" in structured table references, also in VBA, you always refer to active cell in active sheet row. Your loop will populate single cell. So you need either to select cell in proper row when you loop, or refer to specific cell of the table (if you need this): ActiveWorkbook.Sheets("data").ListObjects("data").ListColumns("Error Message").DataBodyRange(i).

combo

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

(OP)
Hi Combo,

I think you might be able to help with this issue since I am researching the @ symbol currently, but don't fully understand the concept. I am currently reading this VBA article https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

here is my error rules formulas on the "error rules" sheet. Is there an issue between the following rule formula and the vba in the background?

Column B = Formula
Column C = Error Message

B2
=IF(data[@Material]="","",IF(LEFT(TRIM([@Material]),2)<>"00","",'error rules'!C$2))

C2
Bare Core must be 5 x 8 and not 8 x 5

B3
=IF(data[@Material]="","",IF(LEFT(TRIM([@Material]),2)<>"LF","",'error rules'!C$3))

C3
Part number starts with LF

here is my full vba code currently:

CODE -->

Sub error_check() ' evaluates rules and adds error log to record

'Application.ScreenUpdating = False

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


r = 2

total_rules = ActiveWorkbook.Sheets("error rules").Cells(Cells.Rows.Count, "C").End(xlUp).Row 'Column "C" / #Model_Number is selected to count due to Column "Error Messge" and Column "Error Formula" being added

material_value = IsEmpty(ActiveWorkbook.Sheets("data").[data[@[Material]]].Value)

Do While r <= total_rules

    If material_value = False Then
    
    Else
    
        ActiveWorkbook.Sheets("data").[data[Error Formula]].Formula = ActiveWorkbook.Sheets("error rules").Cells(r, "B").Value
        
        current_error_msg = ActiveWorkbook.Sheets("data").[data[@[Error Message]]].Value
    
        new_error_msg = ActiveWorkbook.Sheets("data").[data[Error Formula]].Value
 

 
    
    End If
    
Debug.Print current_error_msg
    
    If current_error_msg <> "" Then
        
        
                combined_error_msg = current_error_msg & Chr(10) & new_error_msg
                
                ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = combined_error_msg
            
    Else
            
                ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = new_error_msg
        
        
    End If

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

r = r + 1

Loop



'Application.ScreenUpdating = True

End Sub 

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

For structured tables it means "the same row as for formula cell". Something like relative formulas in R1C1 notatation, or names after removing any of "$" signs in reference - they became relative, keeping the same RC distance.
No problem when you write formula in worksheet and refer to structured table cell in the same row, the address changes to structured table reference and remains the same when you copy up/down, but refers to different row.
In VBA, when you refer to structured table data with "@" in reference, the code assumes that you mean the same row as active cell.

As for your code, still working with structured table, you can get table data size from ActiveWorkbook.Sheets("data").ListObjects("data").DataBodyRange.Rows.Count and loop the rows using For...Next instead Do...Loop. You can also refer to data rows as in the code in my previous post (DataBodyRange is a range, so you can refer to its cells as for other ranges). References with @ are not fixed and depend on active cell.

Also, note that ActiveWorkbook.Sheets("data").[data[Error Formula]].Formula = "" cleans the whole column, ActiveWorkbook.Sheets("data").[data[@[Error Formula]]].Formula = "" only the cell in active cell's row.

combo

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

(OP)
Hi Combo,

Thank you for the fantastic explanation on how @ works.

I am a little confused as to what you are recommending with regards to For / Next instead of Do / Loop. I think you mean that I should count the number of rows in the "data" table and then run the code as a loop per line. Is that correct? If so, what is the advantage to that over the current code method were it runs the rules throughout the entire table at one time?

The formulas return the correct results per loop, but the problem is that at the start of the next loop, the error message is deleted from the "Error Message" column and when the new error message is generated, the new one is not added to the end of the previous message.

With regards to ActiveWorkbook.Sheets("data").[data[Error Formula]].Formula = "" being used, I am using it to clear the formula prior to the next formula being brought in from the "error rules" sheet.

Also, while I am starting to understand the way this is working in more detail, how do I actually correct it? I am playing in waters well beyond the Mariana Trench in my understanding of VBA.

What am I still doing incrrectly?

Thanks,

Mike

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

If you plan to process all data rows, for me it is more readible:

With ActiveWorkbook.Sheets("data").ListObjects("data")
    TotalRows = .DataBodyRange.Rows.Count
    For i = 1 To TotalRows
        ' process error_msg
        current_error_msg = .ListColumns("Error Message").DataBodyRange(i)
        new_error_msg = .ListColumns("Error Formula").DataBodyRange(i)
        ' ...
   Next i
End With 

In your code, test in break mode or execute line by line:
ActiveWorkbook.Sheets("data").[data[Error Formula]].Formula = ""
ActiveWorkbook.Sheets("data").[data[Error Message]].Formula = ""

Clears "Error formula" and "Error message" data, it's ok.

current_error_msg = ActiveWorkbook.Sheets("data").[data[@[Error Message]]].Value
Assigns to current_error_msg value from row same as for active cell, depend on user's selection.

new_error_msg = ActiveWorkbook.Sheets("data").[data[Error Formula]].Value
I haven't check this, from first data cell in column?

Etc.

combo

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

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

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

(OP)
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

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

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
combined_error_msg = current_error_msg & Chr(10) & new_error_msg.
https://www.tek-tips.com/viewthread.cfm?qid=180908...
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

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

(OP)
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.

CODE -->

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

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

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

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

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:

CODE -->

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close