I have a complex program I wrote in Excel 2003 about six years ago, and I
am working to update it. In the program, I am concerned about the sum of
data entered into two columns and totaled in the third column. There are
about 60 groupings of cells, all independent, with about 25 sets of cells
in each grouping. There are six groupings in a single set of three
columns, and 10 sets of columns. A typical formula would be as follows;
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.
Originally I used Excel 2003's inherent data validation with the formula
below. The original program seemed to work fine with Excel 2003. A typical
cell data validation formula in the original program would have been;
=if(countif(A$1:A$25,A5+B5)<=1,"True","False"
This formula would have been repeated over all 25 sets of cells in each of
the 60 groups, with the cell references adjusted as necessary.
In using validation, I want to check that data against other entries in
lines 1-25, columns a-b and c, but I do not want to check the data against
entries in lines 26-50, and vice-versa.
When Excel 2007 came out, the data validation became less dependable - the
users could enter data that totaled the same in, say, line 5 and line 6 of
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 by someone on an Excel group 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
I would very much appreciate some help. I don't know where to go for
assistance. I am sure the solution is not difficult, but I just don't know
how to solve the problem. Thanks to all in advance.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
