Is there an easy way to generate a tracking tab for items found as my VBA code runs

[philwojo]

So I have some code that checks input data for errors and highlights the errors and displays that, it also currently counts the errors and gives a total.

What I'd like to do is add some kind of VBA that when an error is found it would write the specific error to another sheet (tab) in the same WB.

So, what I'd like to happen is as my segments of error checking code find an error they would then call this new VBA code that would write some specific text to this other Tab calling out the Row # that the error was found in and maybe even have a link to the specific cell with the error. I'd also like it to write some text next to the link or row number that gives a description of the error. I'd pass that description based on the type of error found when the segments of my code find it.

There are currently about 20-30 different errors being checked for and some of them can have multiple errors on the same data point (same cell). So I'd like to be able to specify what the specif error is if possible.

Not sure if posting my code would help as it is long and not well written, but it is working. I was hoping to see how some code for this might work and I could then add it to my existing code.

thanks in advance for any help or guidance on this, I'm not really sure how to even try and do this with VBA.

Phil

 

[DanteAmor]

Try putting the following in your code

[COLOR=#0000ff]    'when an error is found it[/COLOR]
    whoja = ActiveSheet.Name                    'your sheet
    wcell = Cells(2, "C").Address(False, False) 'cell with error
    tipo = 2                                    'type of error found
    desc = "description of the error"           'some specific text t
    '
    existe = False
    Set h2 = Sheets("tab error")                'name of tab error
    Set r = h2.Columns("A")
    Set b = r.Find(wcell, LookAt:=xlWhole)
    If Not b Is Nothing Then
        celda = b.Address
        
        Do
            'detalle
            If h2.Cells(b.Row, "B").Value = tipo Then
                existe = True
                Exit Do
            End If
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> celda
    End If
    '
    If existe = True Then
         'If the cell already exists in "tab error", what to do?
    Else
        u2 = h2.Range("A" & Rows.Count).End(xlUp).Row + 1
        h2.Cells(u2, "A").Value = wcell
        h2.Cells(u2, "B").Value = tipo
        h2.Cells(u2, "C").Value = desc
        h2.Hyperlinks.Add Anchor:=h2.Cells(u2, "A"), Address:="", _
            SubAddress:="'" & whoja & "'!" & wcell, TextToDisplay:=wcell
    End If

Let me know if you have difficulty adapting it to your code, then put your complete code.

 

[philwojo]

I'm at the end of my day here, this mostly works, but with my lack of VBA knowledge I am going to ask a question that I know can be done, but it might create more work. Can all of this code be put into it's own SUB and then called when an error is found and have the necessary information passed?

If that is a lot more work then I'm not worried about it, but it would be nice to have a CALL statement in my code blocks rather than all of this code each time.

Also, it is passing the wrong value for the Cell location, I haven't looked closely at your code yet, again end of my work day here, but how can I make it match where the error is found during my sections of code?

Thanks for the great start, this is really helpful!

Phil

 

[philwojo]

I figured out how to make it point to my cell, sorry just hadn't looked at the code closely yet.

Phil

 

[DanteAmor]

Yes, you can put somethig like:

'found an error
call Fill_Tab_Error("B10", num_tipo, "description error")

---

sub Fill_Tab_error(wcell, tipo, desc)

    'when an error is found it
    whoja = ActiveSheet.Name                    'your sheet
    'wcell = Cells(2, "C").Address(False, False) 'cell with error
    'tipo = 2                                    'type of error found
    'desc = "description of the error"           'some specific text t
    '
    existe = False
    Set h2 = Sheets("tab error")                'name of tab error
    Set r = h2.Columns("A")
    Set b = r.Find(wcell, LookAt:=xlWhole)
    If Not b Is Nothing Then
        celda = b.Address
        
        Do
            'detalle
            If h2.Cells(b.Row, "B").Value = tipo Then
                existe = True
                Exit Do
            End If
            Set b = r.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> celda
    End If
    '
    If existe = True Then
         'If the cell already exists in "tab error", what to do?
    Else
        u2 = h2.Range("A" & Rows.Count).End(xlUp).Row + 1
        h2.Cells(u2, "A").Value = wcell
        h2.Cells(u2, "B").Value = tipo
        h2.Cells(u2, "C").Value = desc
        h2.Hyperlinks.Add Anchor:=h2.Cells(u2, "A"), Address:="", _
            SubAddress:="'" & whoja & "'!" & wcell, TextToDisplay:=wcell
    End If


End Sub

 

[philwojo]

Ok, thanks for the updates, I'm trying to get this to work and struggling a little bit. Here is what I have for the Call:

'found an error                        
                        Set Cellerror = Cell2.Address(False, False)  'cell with error
                        Call Fill_Tab_error(Cellerror, "2100 Record, field [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL]  has an error", "A value of 1.0 is required, something else is in this field.  Check for leading/trailing spaces if nothing is obvious.")

I define Cellerror as a Variant (Dim Cellerror as Variant), Cell2 is a Range
But when I run the code I get a "Complie Error: Object Required" and it highlights the "set Cellerror..." line from above.

I'm trying to work through it but I can't seem to get it to work.

When I use the following code, without a call, then it works.

'when an error is found it
    whoja = ActiveSheet.Name                    'your sheet
    wcell = Cell2.Address(False, False) 'cell with error
    tipo = "2100 record, field [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2"]#2[/URL]  has an error"                                   'type of error found
    desc = "A value of 1.0 is required, something else is in this field.  Check for leading/trailing spaces if nothing is obvious."           'some specific text t
    '
    existe = False
    Set h2 = Sheets("Potential Errors")                'name of tab error
    Set R = h2.Columns("A")
    Set b = R.Find(wcell, LookAt:=xlWhole)
    If Not b Is Nothing Then
        celda = b.Address
       
        Do
            'detalle
            If h2.Cells(b.Row, "B").Value = tipo Then
                existe = True
                Exit Do
            End If
            Set b = R.FindNext(b)
        Loop While Not b Is Nothing And b.Address <> celda
    End If
    '
    If existe = True Then
         'If the cell already exists in "tab error", what to do?
    Else
        u2 = h2.Range("A" & Rows.Count).End(xlUp).Row + 1
        h2.Cells(u2, "A").Value = wcell
        h2.Cells(u2, "B").Value = tipo
        h2.Cells(u2, "C").Value = desc
        h2.Hyperlinks.Add Anchor:=h2.Cells(u2, "A"), Address:="", _
            SubAddress:="'" & whoja & "'!" & wcell, TextToDisplay:=wcell
    End If

You can see I use Cell2 in your original code to set "wcell" to it to pass the cell location, and that worked without any issues.

Any thoughts?

Again, thanks for your assistance so far.

Phil

 

[philwojo]

Ok, changed it to this, and it worked.

                        'found an error
                        'Set Cellerror = Cell2.Address(False, False)  'cell with error
                        Call Fill_Tab_error(Cell2.Address(False, False), "2100 Record, field [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL]  has an error", "A value of 1.0 is required, something else is in this field.  Check for leading/trailing spaces if nothing is obvious.")

Thought i'd give it a try, but not sure I understand why this worked and the other way didn't if anyone can help explain I'd appreciate it.

Phil

 

[DanteAmor]

sub Fill_Tab_error(wcell, tipo, desc)

The first parameter must be a string, e.g. "B2" or "D15", then Cell2.Address(False, False) retrun some like "C3"

If you send:

Set Cellerror = Cell2.Address(False, False)

you are sending a range.

 

[philwojo]

Ok, forgive my ignorance, how can I make it a string. I know I can research it but busy with work, so just trying to get a quick answer.

But, otherwise it is doing exactly what I need, I really appreciate your help with this!

Phil

 

[DanteAmor]

This is the correct way:
Cell2.Address(False, False)

The code I sent you works with the string
You do not need to send the cell, only the address of the cell as string