Error Table For Excel Comparisons

[ajocius]

This is a toughy I believe. For the past few weeks my evenings are
learning and working on a large macro to compare and verify correct use
of data in two spreadsheets within the same workbook. What I'd like to
do is create a lookup table which has my error messages in it. To go
along with the error messages its important to plant what the error is.
Example below:

Error Table -

Error 1: A, Cell B = C, Value is greater than 90
Error 2: A, Cell B = C, Value is less than 50
....
....
.... etc. - where A=sheet name, B=Cell and C= value in Cell

Listing reports error in log for error 1 (text file) below:

Error 1: Sheet1, Cell A1 = 91, Value is greater than 90

Am I making this more complex than it really is?


Learning VBA quickly and enjoying it......

Tony

 

[Bernie Deitrick]

Personally, I would create the error message on the fly, as the comparisons are made. For example,
with your first two messages:

For Each mySht In ActiveWorkbook.Worksheets
For Each myCell In mySht.Range("A1:A100")
If myCell.Value > 90 Then
strErrMsg = mySht.Name & ", Cell " & _
myCell.Address(False, False) & _
" = " & myCell.Value & _
", Value is greater than 90"
MsgBox strErrMsg
ElseIf myCell.Value < 50 Then
strErrMsg = mySht.Name & ", Cell " & _
myCell.Address(False, False) & _
" = " & myCell.Value & _
", Value is less than 50"
MsgBox strErrMsg
End If
Next myCell
Next mySht

HTH,
Bernie
MS Excel MVP