Displaying the address of the cell in error

[JenniferMurphy]

I am working on a UDF that processes a large table of data. A small example might look something like this.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]AA[/TD]
[TD="align: center"]AB[/TD]
[TD="align: center"]AC[/TD]
[TD="align: center"]AD[/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: center"]$100[/TD]
[TD="align: center"]4.5[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]B33[/TD]
[TD="align: center"]3/22[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]2005[/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD="align: center"]$250[/TD]
[TD="align: center"]5.8[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]X21[/TD]
[TD="align: center"]4/15[/TD]
[TD="align: center"]135[/TD]
[TD="align: center"]2011[/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"]$75[/TD]
[TD="align: center"]1.5[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]R03[/TD]
[TD="align: center"]3/29[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1995[/TD]
[/TR]
[TR]
[TD="align: center"]27[/TD]
[TD="align: center"]$110[/TD]
[TD="align: center"]6.0[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]G44[/TD]
[TD="align: center"]4/20[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]2015[/TD]
[/TR]
</tbody>[/TABLE]
The UDF is passed the table range

=DoIt(W24:AD27)

The UDF loops through the rows and columns until it completed the work or encounters an error.

Public Function DoIt(Table as range)

Dim iCol as Integer, iRow as Integer
For iRow = 1 to RowN
  For iCol = 1 to ColN

     . . . code processing table cells

  Next iCol
Next iRow

End Function

In the table above, there is a missing value in AC26. The error occurs when iRow=3 and iCol=7. But these are relative to the first cell in the table. I would like to be able to calculate the actual cell address (AC26 or $AC$26) and show that in a MsgBox.

Can someone help me with the code I need to do that?

Thanks

 

[mikerickson]

You could add a error handler to the UDF

Public Function DoIt(Table As Range)
    Dim iCol As Integer, iRow As Integer
    Dim RowN As Long, ColN As Long

    [COLOR="#FF0000"]On Error GoTo ErrorHaNdler[/COLOR]

    For iRow = 1 To RowN
      For iCol = 1 To ColN

         ' . . . code processing table cells
    
      Next iCol
    Next iRow
Exit Function

ErrorHaNdler:
    Dim errString As String

    errString = Err & vbCr & Error
    errString = errString & vbCr & "in " & Cells(iRow, iCol).Address
    errString = errString & vbCr & "Continue or Cancel"
    
    Select Case MsgBox(errString, vbAbortRetryIgnore + vbDefaultButton3)
        Case vbAbort
            Exit Function
        Case vbRetry
            Resume
        Case vbIgnore
            Resume Next
    End Select
End Function

I noticed that RowN and ColN weren't declared.

 

[JenniferMurphy]

You could add a error handler to the UDF

I don't need a runtime error handler because the errors are invalid data.

I couldn't quite follow most of that code, but I was able to extract the key expression. This code now does what I need it to do.

Public Function DoIt(Table As Range)
Dim iCol As Integer, iRow As Integer
Dim RowN As Long, ColN As Long
Dim ErrAddr as string

For iRow = 1 To RowN
  For iCol = 1 To ColN

      . . .

    If (some test) then
      ErrAddr = Table(iRow, iCol).Address
      Msgbox "Error XYZ in cell " & ErrAddr
      DoIt = CVErr(xlErrValue)
      Exit Function
    End If

       . . .

    Next iCol
Next iRow

End Function

Thank you very much for that.

I noticed that RowN and ColN weren't declared.

Really? Line 3 doesn't qualify?