Re: $$Excel-Macros$$ VBA for Data Validation Question
Hello Deepak
While trying to figure out how to post everything you need, I found a
solution to my problem. By trial and error, I stumbled on a VBA code
solution that works as follows;
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
Select Case True
Case Not Intersect(Target, Me.Range("B2:B20")) Is Nothing
If Application.CountIf(Range("D2:D20"),
(Range(Target.Address).Value + Range(Target.Address).Offset(0, 1).Value)) >
1 Then _
If MsgBox("Sum already used, accept anyway?",
vbYesNo + vbQuestion) = vbNo Then .Value = ""
Case Not Intersect(Target, Me.Range("C2:C20")) Is Nothing
If Application.CountIf(Range("D2:D20"),
(Range(Target.Address).Value + Range(Target.Address).Offset(0, -1).Value))
> 1 Then _
If MsgBox("Accepting this answer will cause a tie
score. Do you want to accept it anyway?", vbYesNo + vbQuestion) = vbNo
Then .Value = ""
' >>> I will add in the other groups here<<<
End Select
ws_exit:
Application.EnableEvents = True
End Sub
The code above is not exactly what I will use, but I think you can see how
the solution will work for me. I tried to abbreviate it a bit for this
forum. Also, I believe that by using the offset property, I can keep the
coding to a minimum. Thank you so much for your help - you set me on the
right path!
Bob
On Friday, August 2, 2013 2:11:42 AM UTC-4, barnwal wrote:
> PFA
>
> Is this what you are looking for. If not send an example file.
>
> Regards
>
> Deepak
> On Thu, Aug 1, 2013 at 10:26 PM, RJQMAN >wrote:
>
>> I am not sure if this is the proper way to reply, so forgive me if I am
>> not doing this correctly. I thought I posted the code but perhaps I did not
>> do it correctly.
>> My cell formula was simple in column C - I just use Isnumber to check
>> and make sure that there was a number in both column A and Column B before
>> totalling the two columns.
>> =if(and(isnumber(A5),isnumber(B5)), A5+B5,"")
>> Your approach works except
>> > I want the user to be able to accept the duplicate number in column 3
>> if they want to - I just want to alert them that the duplicate number could
>> be an error in data entry and they should check to make sure it is not an
>> error.
>> If I could display a message box or something like that if there is a
>> duplicate in column C and give the user the option to accept it, it would
>> solve my problem! I could probably move your cell formulas into VBA as an
>> application, but perhaps there is a better way??
>> Many thanks,
>> Bob Q.
>>
>> On Thursday, August 1, 2013 8:29:30 AM UTC-4, De Premor wrote:
>>
>>> Just trying to imagine with your problem since there is no file
>>> attached to explore the other scene.
>>>
>>> PFA my first approach, is that close with your ?
>>>
>>> Pada 01/08/2013 8:54, RJQMAN menulis:
>>>
>>> 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 (forg
Re: $$Excel-Macros$$ VBA for Data Validation Question
PFA Is this what you are looking for. If not send an example file. Regards Deepak On Thu, Aug 1, 2013 at 10:26 PM, RJQMAN wrote: > I am not sure if this is the proper way to reply, so forgive me if I am > not doing this correctly. I thought I posted the code but perhaps I did not > do it correctly. > My cell formula was simple in column C - I just use Isnumber to check > and make sure that there was a number in both column A and Column B before > totalling the two columns. > =if(and(isnumber(A5),isnumber(B5)), A5+B5,"") > Your approach works except > > I want the user to be able to accept the duplicate number in column 3 > if they want to - I just want to alert them that the duplicate number could > be an error in data entry and they should check to make sure it is not an > error. > If I could display a message box or something like that if there is a > duplicate in column C and give the user the option to accept it, it would > solve my problem! I could probably move your cell formulas into VBA as an > application, but perhaps there is a better way?? > Many thanks, > Bob Q. > > On Thursday, August 1, 2013 8:29:30 AM UTC-4, De Premor wrote: > >> Just trying to imagine with your problem since there is no file >> attached to explore the other scene. >> >> PFA my first approach, is that close with your ? >> >> Pada 01/08/2013 8:54, RJQMAN menulis: >> >> 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
Re: $$Excel-Macros$$ VBA for Data Validation Question
I am not sure if this is the proper way to reply, so forgive me if I am not doing this correctly. I thought I posted the code but perhaps I did not do it correctly. My cell formula was simple in column C - I just use Isnumber to check and make sure that there was a number in both column A and Column B before totalling the two columns. =if(and(isnumber(A5),isnumber(B5)), A5+B5,"") Your approach works except > I want the user to be able to accept the duplicate number in column 3 if they want to - I just want to alert them that the duplicate number could be an error in data entry and they should check to make sure it is not an error. If I could display a message box or something like that if there is a duplicate in column C and give the user the option to accept it, it would solve my problem! I could probably move your cell formulas into VBA as an application, but perhaps there is a better way?? Many thanks, Bob Q. On Thursday, August 1, 2013 8:29:30 AM UTC-4, De Premor wrote: > > Just trying to imagine with your problem since there is no file attached > to explore the other scene. > > PFA my first approach, is that close with your ? > > Pada 01/08/2013 8:54, RJQMAN menulis: > > 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
Re: $$Excel-Macros$$ VBA for Data Validation Question
Just trying to imagine with your problem since there is no file attached
to explore the other scene.
PFA my first approach, is that close with your ?
Pada 01/08/2013 8:54, RJQMAN menulis:
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
$$Excel-Macros$$ VBA for Data Validation Question
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
