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 <rjq...@gmail.com> 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 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<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 excel-macros...@**googlegroups.com.
>> To post to this group, send email to excel-...@googlegroups.com.
>>
>> Visit this group at
>> http://groups.google.com/**group/excel-macros<http://groups.google.com/group/excel-macros>
>> .
>> For more options, visit
>> https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
>> .
>>
>>
>>
>>
>> --
> 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 excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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 excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
Book1.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
