[µ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¤è®×. ÁÂÁÂ!

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