[µo°Ý] VBA PostCode format Validation problem!!

hildaliu

½Ð°Ý¦³¤°»ò¤èªk¥ÎExcel VBA  ÅçÃÒ­^°ê¶l¬F½s½X,
¦r¥À+¼Æ¦rªºªø«×¬O 5 -7 digit
¶l¬F½s½X¬OÀ³¸Ó±Ä¥Î¥H¤U®æ¦¡¡G
LN space  NLL
LNN space  NLL
LLN space  NLL
LNL  space  NLL
LLNL space  NLL
LLNN space  NLL
L¬O­^¤å¦r¥À¡AN¬O­^¤å¡C¦pªG±ø¥ó¤£¥¿½T¡A·|Åã¥ÜMsgbox "³o¬O¤£¥¿½Tªº¶l½s"
§Ú¤£ª¾¹D«ç¼Ë¥Î VBA  ÅçÃÒ¥H¤Wªº®æ¦¡,¦]¬°®æ¦¡¤Ó¦h
§Æ±æ¦³°ª¤â¥i¥HÀ°§U§Ú,µ¹§Ú¤@­Ó¸Ñ¨M¤è®×. ÁÂÁÂ!

stillfish00

¦^´_ 1# hildaliu
¼g¤F¤@­ÓFunction§PÂ_¡A¥t¥~®Ú¾Úwiki¦h¥[¤F§PÂ_±ø¥óGIR NLL

1. Function IsUKPostcode(sText As String) As Boolean
   
2.   Dim oRegexp As Object
   
3.   
   
4.   Set oRegexp = CreateObject("vbscript.regexp")
   
5.   With oRegexp
   
6.     .ignoreCase = False
   
7.     .Pattern = "([A-Z]{1,2}\d(\d|[A-Z])?|GIR) \d[A-Z]{2}"
   
8.   End With
   
9.   
   
10.   IsUKPostcode = oRegexp.Test(sText)
    
11. End Function

½Æ»s¥N½X

test

1. Sub Test()
   
2.   Dim ar, x
   
3.   ar = Array("F22 5BQ", "FJ29 1GH", "FJ00", "E0 3KK")
   
4.   
   
5.   For Each x In ar
   
6.     If Not IsUKPostcode(CStr(x)) Then MsgBox "³o¬O¤£¥¿½Tªº¶l½s:" & x
   
7.   Next
   
8. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 2# stillfish00
¤£¦n·N«ä¡A2# pattern µy·L§ï¤@¤U¡A¥H¤Ç°t¾ã­Ó¦r¦ê¦Ó¤£¬O¦r¦ê¥]§t¶l»¼°Ï¸¹¡A³o¼Ë¤ñ¸û¦X²z¡C
.Pattern = "^([A-Z]{1,2}\d(\d|[A-Z])?|GIR) \d[A-Z]{2}$"

hildaliu

¦^´_ 2# stillfish00

·PÁ±zªºÀ°§U!
§A¥i¥H¬°§Ú¸ÑÄÀ³o¥N½X¬O¦p¦ó¾Þ§@?
§Ú¤£¬O¤Q¤À©ú¥Õ
«D±`·PÁÂ!

hildaliu

¦^´_ 2# stillfish00


¦pªG§Ú¬O·QÅçµý§Úªºexcel order form sheet ¸Ìªºrange ¡]h19¡^customer post code
¨ºvba ­n«ç¼Ë¼g¡H
§Ú¬O·Q«öbutton¨ÓÅçÃÒpost code

Sub validation

If range¡]h19¡^= IsUkpostcode then
Msgbox "valid post code"
Else
Msgbox" invalid post code"

End sub

§Ú¤W­±ªºcode¬O¤£¹ïªº¡A§Ú¤£ª¾¹D«ç¼Ë­×§ï
§Ú¬O·s¤â¡A¦b¾Çvba
³Â·Ð¨ì§A¡A¤£¦n·N«ä

hildaliu

¦^´_  stillfish00


¦pªG§Ú¬O·QÅçµý§Úªºexcel order form sheet ¸Ìªºrange ¡]h19¡^customer post code ...
hildaliu µoªí©ó 2014-11-18 21:49

i just tried this code , is this correct or not?

1. Sub UK_Postcodes()
   
2. Dim RegExp As Object, Collection As Object, RegMatch As Object
   
3. Dim Myrange As Range, C As Range, Outstring As String
   
4. Set RegExp = CreateObject("vbscript.RegExp")
   
5. With RegExp
   
6. .Global = False
   
7. .Pattern = "^([A-Z]{1,2}\d(\d|[A-Z])?|GIR) \d[A-Z]{2}$"
   
8. End With
   
9. Set Myrange = Range("h19")
   
10. Outstring = ""
    
11. Set Collection = RegExp.Execute(ActiveCell.Value)
    
12. For Each RegMatch In Collection
    
13. Outstring = Outstring & RegMatch
    
14. Next
    
15. 
    
16. If Range("h19").Value <> "" And Range("h19").Value = Outstring Then
    
17. MsgBox "Valid UK Postcode"
    
18. Else
    
19. MsgBox "Invalid UK Postcode"
    
20. End If
    
21. Set Collection = Nothing
    
22. Set RegExp = Nothing
    
23. Set Myrange = Nothing
    
24. End Sub

½Æ»s¥N½X

stillfish00

1. Sub Validation()
   
2.   If IsUKPostcode(Range("H19").Text) Then
   
3.     MsgBox "valid post code"
   
4.   Else
   
5.     MsgBox " invalid post code"
   
6.   End If
   
7. End Sub

½Æ»s¥N½X

¦^´_ 5# hildaliu