How To Detect When # Of Characters In One Column Does Not Match # Defined In Other Column

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,273
.....unfortunately [validation] wouldn't work for this application, since temporarily if the amount of letters are wrong, it would be nice if that were "allowed", but I would need it to quickly notify the user with a popup as soon as they enter the error, ....CG
I just remembered that setting Validation to Warning or Info rather than Stop, will allow a bad entry after the user is notified.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,421
Office Version
365
Platform
Windows
Try this in the 'ThisWorkbook' module. It checks for changes in column J (rows 3+) on any sheet and then does the check on column B and the length of the column J entry and displays a message with details if there are any inconsistencies. If the user enters multiple entries at once (eg with Ctrl+Enter or Paste) the code should check all the entered values and report on multiple items if incorrect.

Suggest a test on a copy of your workbook, or maybe even a simplified workbook as your real one sounds fairly big.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> cr <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> bl <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, jl <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> msg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FoundError <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> cr = Intersect(Target, Sh.Columns("J"), Sh.Rows("2:" & Sh.Rows.Count))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> cr <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        msg = "Please check the following:" & vbLf & vbLf & _<br>                "Cell" & vbTab & "Value" & vbTab & "Length" & vbTab & "Expected Length"<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> cr<br>            bl = c.Offset(, -8).Value<br>            jl = Len(c.Value)<br>            <SPAN style="color:#00007F">If</SPAN> bl <> 0 And bl <> jl <SPAN style="color:#00007F">Then</SPAN><br>                msg = msg & vbLf & c.Address(0, 0) & vbTab & c.Value & vbTab & _<br>                            jl & vbTab & bl<br>                FoundError = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        <SPAN style="color:#00007F">If</SPAN> FoundError <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox msg<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

chatguy

New Member
Joined
Oct 15, 2007
Messages
10
Thanks soooo much, Peter! (I didn't realize it would be this much code; I really hope it didn't take too long to figure out.) Thank you so much again; I'll try it out!

-cg
 

Watch MrExcel Video

Forum statistics

Threads
1,095,623
Messages
5,445,559
Members
405,339
Latest member
dayanand008

This Week's Hot Topics

Top