Forum Discussion
how to validate the last four digits ?
can anybody help me ?
I have my code here, but I get no response, if I type etc. 020202-1326
I need to validate the last 4 digits of this string xxxxxx-xxxx with hyphen between 6 and 7 digits.
I have a rule, that if the first 2 numbers in the last 4 digits are odd, then the last 2 numbers must also be odd, e.g. 070707-1337 is correct while 070707-1926 is incorrect.
Conversely, if the first 2 numbers in the last 4 digits are even, then the last 2 numbers must also be even, e.g. 070707-1426 is correct but 070707-1423 is incorrect
My code looks like this, but no msgbox popup ?
Dim from As Worksheet
Set frm = ThisWorkbook.Sheets("Enter data")
Dim strValue As String
strValue = CStr(frm.Range("I6").Text)
strValue = Replace(strValue, "-", "")
Dim firstPair As Integer
Dim secondPair As Integer
firstPair = CInt(Mid(strValue, 7, 2))
secondPair = CInt(Mid(strValue, 9, 2))
If (firstPair Mod 2 = 0 And secondPair Mod 2 <> 0) Or (firstPair Mod 2 <> 0 And secondPair Mod 2 = 0) Then
MsgBox "The last four digits of the number must be even or odd.", vbOKOnly + vbInformation, "number"
frm.Range("I6").Select
frm.Range("I6").Interior.Color = vbRed
frm.Range("I6").Font.Bold = True
frm.Range("I6").Font.Color = vbWhite
frm.Range("I6").Font.Name = "Arial"
frm.Range("I6").Value = "Enter a valid number"
Validate = False
Exit Function
Than If
2 Replies
KewiDK
Apart from you last line of code, there is also some typos on some line,- Corrected the variable name: You declared a variable from but used frm in your code. I corrected the declaration to Dim frm As Worksheet
- Removed the CStr function: The CStr function is not necessary when you’re getting the text from a range. So, I removed it and directly assigned the text to strValue.
Here’s the comparison for clarity:
Your code:
Dim from As Worksheet Set frm = ThisWorkbook.Sheets("Enter data") Dim strValue As String strValue = CStr(frm.Range("I6").Text) ... Than Ifhere's what I did:
Dim frm As Worksheet Set frm = ThisWorkbook.Sheets("Enter data") Dim strValue As String strValue = frm.Range("I6").Text ... End If
