Categorize errors

jogischika · June 25th, 2013, 05:47 AM

Hi,

I have a VBA problem which is a bit above my knowledge at the moment.

From a program, I get a log file which contains error messages (300+ errors, one error per row). This looks similar to this:

Calculation with "Test1" for Template not possible
Calculation with "Test2" for Report not possible
No Info found for XY
No Details found for YZ
Etc.

I would like to paste this log file into a sheet and use a macro to categorize the errors and count how many errors of each category occured. I have a catalogue of every possible error plus its category in a separate sheet. The results of the search should be entered into an existing table. This could look like this:

ErrorCategory1: 7 Errors
ErrorCategory2: 15 Errors
Etc.

This data should then be used to draw a chart, illustrating the results.

I see two possible ways to do that (not thinking in code): Either compare the list of errors with "column A" of the error catalogue, identify the category name in "column B" and count the hits for each category or implement a specific search with condition and category for each possible error in the code.
The first way seems to be more elegant and open for additional errors (in case the catalogue missed some errors I did not know) but more complicated as well.
Can I use a CountIf formula here? How could this be cunstructed?

I know this is a big question but I would be very happy about every hint.

Best Regards,
Rob

Shasur · June 25th, 2013, 10:50 PM

Hi Rob

You can either import or Open the Text file (Log file) in Excel.

Since the log file doesn't contain Error Code, you have do the text matching with your database. This is a tricky part.

I am not sure how you have stored the Error Category and its content in the Error database.

Assuming there is an Error Category - Cat1 and its corresponding error text Report not possible ..

You can loop through the Error Text and can updated all the related text in Excel with the category

Find or FindAll function should help you

http://www.ozgrid.com/VBA/find-method.htm
http://vbadud.blogspot.in/2007/10/ex...ll-method.html

If your catalogue is an Excel file you can use this like a Database itself too (http://vbadud.blogspot.in/2008/05/us...-database.html)

Your updated Logfile will now have Error Category listed in Column B . A Sum/SubTotal will give you the count

Cheers
Shasur

jogischika · June 26th, 2013, 04:01 AM

Hey Shasur,

thank you for your reply! The DB solution looks very interesting but quite complex as well. I will try to work my way through that out of interest.

It seems like I have misunderstood parts of what I have to do as well. What I want to do now is the following.

Search Column A in Sheet "Log Input" for error message with changing data replaced by wildcards
Whenever the search hits a result, I want to write fixed data into an existing table in the sheet "Results" starting in the first empty row.
This data consists partly of cell content from the log (via mid-function) and of data that I want to specifically name in the code for each error message I am searching for (like ErrorNr. XYZ) so not by grabbing it from a database or error catalogue
When this process is finished, it should search for the next result and write the information from this hit into the first empty row in the "results" table
And so on for this error, then the same procedure for the next error

To be more clear, the errors look somehow like this (and I want the info between the "" for each result)

Calculation with "Test1" for "Template" with Number "12345" not possible in the area "987654".

So the resulting questions are:

What function do I use for the search loop? "For Each Next" plus "Find"? If yes, how?
How do I implement the grabbing of various information from the current "search result cell" and writing it into other cells, which are all in one row ("$A?:$G?"; with ? being the index of the row)?
How can I implement the rule that for every new result, the data should be written into the frist empty row in the results table?

I hope these questions are clear and I am looking forward to your ideas.

Regards
Rob

Btw: Where do I best upload a sample of my project?

Shasur · June 26th, 2013, 05:36 AM

Hi

What function do I use for the search loop? "For Each Next" plus "Find"? If yes, how?

Let's assume that the Error Text and Categories are available in Result Sheet/Workbook in column A and Column B

E.g,

Error Text Error Number
Report not possible Category 1
Template not possible Category 2

You need to loop through all the cells .. You can know the last non-empty cells in Result sheet by using Specialcells like

Code:

For i1= 2 To Workbooks(1).Sheets("Result")Cells.SpecialCells(xlCellTypeLastCell).Row
        sText = Trim$(Cells(i1, 1).Value)
        sErrCategory = Trim$(Cells(i1, 2).Value)
...
'Find code will be here

How do I implement the grabbing of various information from the current "search result cell" and writing it into other cells, which are all in one row ("$A?:$G?"; with ? being the index of the row)?

Here is how you can write the values

Code:

ActiveSheet.Cells(1, 1).Value = "XX1"
ActiveSheet.Cells(1, 2).Value = "XX2"

How can I implement the rule that for every new result, the data should be written into the frist empty row in the results table?

This is the last thing to be automated. Finding the last cell can be done using Specialcells or any other methid

But you need to double check before entering to Results as it is a 'Database'

Cheers
Shasur

jogischika · June 26th, 2013, 06:09 AM

Hey Shasur,

thank you for your input. I have gotten further already but not as automated as I want it to be. Here is my code. Sorry for the German comments.
All this does is checking the log for 2 error messages and pasting info into the "Auswertung" Sheet.
Before the pasting, I want to put a function to find the first empty row, which i would then implement into the ranges instead of "B2" "Bvariable".

Code:

Sub Categorize

Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
With Sheets("Log Input").Range("A1:A100")
    Set LastCell = .Cells(.Cells.Count)
End With
Set FoundCell = Sheets("Log Input").Range("A1:A100").Find(what:="?????Aufriss nach 'Partner' mit Wert 'G????-BETEILIGUNG' fÃ¼r Pos. ?????*", after:=LastCell)

If Not FoundCell Is Nothing Then
    FirstAddr = FoundCell.Address
            Sheets("Auswertung").Range("B2").Value = Mid(FoundCell, 6, 68)                          'Kopiert relevanten Teil der Meldung in Feld "Nachricht"
            Sheets("Auswertung").Range("A2").Value = "/CCDEV01/CCCS092"                             'Spezifische Nachrichtennummer
            Sheets("Auswertung").Range("C2").Value = "Fehler"                                       'Festgelegter Typ
            Sheets("Auswertung").Range("D2").Value = "Aufriss mit Wert fÃ¼r Pos. nicht erlaubt"      'Festgelegte Kategorie
            Sheets("Auswertung").Range("E2").Value = Mid(FoundCell, 19, 8)                          'Gibt "Partner" aus
            Sheets("Auswertung").Range("F2").Value = Mid(FoundCell, 38, 18)                         'Gibt Partnerwert aus
            Sheets("Auswertung").Range("G2").Value = Mid(FoundCell, 68, 8)                          'Gibt Position aus
End If
Do Until FoundCell Is Nothing
    Debug.Print FoundCell.Address
    Set FoundCell = Sheets("Log Input").Range("A1:A100").FindNext(after:=FoundCell)
    If FoundCell.Address = FirstAddr Then
        Exit Do
    End If
Loop

    With Sheets("Log Input").Range("A1:A100")
        Set LastCell = .Cells(.Cells.Count)
    End With
    Set FoundCell = Sheets("Log Input").Range("A1:A100").Find(what:="?????Aufriss nach 'Frist' mit Wert '??' fÃ¼r Pos. ?????", after:=LastCell)
    
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
            Sheets("Auswertung").Range("B3").Value = Mid(FoundCell, 6, 69)                          'Kopiert relevanten Teil der Meldung in Feld "Nachricht"
            Sheets("Auswertung").Range("A3").Value = "/CCDEV01/CCCS092"                             'Spezifische Nachrichtennummer
            Sheets("Auswertung").Range("C3").Value = "Fehler"                                       'Festgelegter Typ
            Sheets("Auswertung").Range("D3").Value = "Aufriss mit Wert fÃ¼r Pos. nicht erlaubt"      'Festgelegte Kategorie
            Sheets("Auswertung").Range("E3").Value = Mid(FoundCell, 20, 5)                          'Gibt "Partner" aus
            Sheets("Auswertung").Range("F3").Value = Mid(FoundCell, 37, 2)                          'Gibt Partnerwert aus
            Sheets("Auswertung").Range("G3").Value = Mid(FoundCell, 50, 10)                         'Gibt Position aus
    End If
    Do Until FoundCell Is Nothing
        Debug.Print FoundCell.Address
        Set FoundCell = Sheets("Log Input").Range("A1:A100").FindNext(after:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
    Loop

End Sub

Can you work with that?

Best
Rob

jogischika · June 26th, 2013, 11:20 AM

Hey everyone,

the problem is solved.

Thanks a lot!
Rob