I have a complex program written six years ago, and I am working to update it. In the program, I am concerned about the sum of data entered into two ranges;

Column A5 - 15
Column B5 - 20
Column C5 contains a simple excel formula that adds column A and Column B and displays the sum = 35

If the user enters other numbers into column A and column B such that they total the same as a previous entry anywhere in the first 25 lines, I want to alert the user that the entry may be in error.

The original program used data validation and seemed to work fine. A typical cell formula would be

=if(countif(A$1:A$25,A5+B5)<=1,"True","False"

Another totally independent group of data is entered in cells A$30-B$50, etc, with over 60 different groups on the worksheet. I want to check that data against other entries in lines 30-50, but I do not want to check the data against entries in lines 1-25, and vice-versa.

I wrote the original program in Excel 2003, and it worked just fine. When Excel 2007 came out, the data validation became less dependable - many times a user could enter data that totaled the same in the first 25 lines, but for reasons I never understood, the entry did not trigger the alert in the Excel Data Validation.

I want to fix this in the revised program, so I have been testing a VBA solution someone provided for me back in 2007. It works pretty well, but the code that the person provided me (forgive me, I do not remember who it was) is dependent on the 'countif' evaluating the entire column of data to search for a duplicate, and I want the countif to evaluate the first 25 lines. I want to use a second countif to evaluate the next 25 lines, and so forth through all 60 groupings on the sheet. I have been trying to modify this code without success for several days, and although it looks like it should work, it never does! Just when I get everything to plug in in a way that appears correct, the code does not work at all. I am at a loss as to what to do.

Could someone please tell me how to make this work? I like using VBA, because I can vary the output messages as the program is used in different venues, so I would prefer to have the validation in VBA. I am using worksheet change to trigger the code.

Here is a portion of the code that I am working with (I took out some non-related items), which seems to work fine, except that it evaluates an entire column instead of a portion of the column. I have the columns as variables so that I do not have to rewrite the code for each of the sixty sections.

The real code has a counter that goes much higher, of course, but this hopefully is enough information for someone with more knowledge that I have to help me solve this issue. I have tried to substitute for the "Me.columns(TotalsColumn) and that is where I get into trouble. Not sure if I need the error escape lines or not, but I would rather fail to catch a duplicate than have the entire program crash, so I have them in there.

I cannot figure out how to do make it work though. Can someone please help me?


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TotalsColumn As Integer
Dim TestColumn1 As String
Dim TestColumn2 As String

Counter = 0
Dim range2 As String

Do Until Counter = 2

If Counter = 0 Then Const WS_RANGE As String = "A1:B25": TestColumn1 = "A": TestColumn2 = "B": TotalsColumn = 3
If Counter = 1 Then Const WS_RANGE As String = "A26:B50": TestColumn1 = "A": TestColumn2 = "B": TotalsColumn = 3
If Counter = 2 Then Const WS_RANGE As String = "D1:D25": TestColumn1 = "D": TestColumn2 = "E": TotalsColumn = 6

'( etc. for 59 more sections in various columns - six sections to a column)...

On Error GoTo ws_exit
If Target = 0 Then GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Application.CountIf(Me.Columns(TotalsColumn), Me.Cells(.Row, TestColumn1).Value + Me.Cells(.Row, TestColumn2).Value) = 1 Then
MsgBox "Valid Entry"
Else
On Error GoTo ws_exit
If MsgBox("Sum already used, accept anyway?", vbYesNo + vbQuestion) = vbNo Then .Value = ""
End If
End With
End If
Counter = Counter + 1
Loop

ws_exit:
Application.EnableEvents = True
End Sub