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

[chatguy]

Hi All,

I'm hoping this would be easy to do, but would anyone be able to figure out how to create a macro that:

- In each row, pops up a Warning Dialog Box if the amount of letters in column "J" doesn't match the # defined in column "B"?
- Preferably this would pop up the Dialog Box as soon as the error occurs (so it's always running)

Two Exceptions:
- Does not look at Rows 1 & 2
- Does not look at Rows where column B = 0 or null

Thanks so much!
CG

 

[mikerickson]

.....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.

 

[Peter_SSs]

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]

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