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


"ajocius" <ajocius.1ss2qo_1122383185.9983@excelforum-nospam.com> wrote in message
news:ajocius.1ss2qo_1122383185.9983@excelforum-nospam.com...
>
> 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
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=390162
>