ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA§PÂ_§ä¥X²Å¦X±ø¥ó³Ì¤j­È

[µo°Ý] VBA§PÂ_§ä¥X²Å¦X±ø¥ó³Ì¤j­È

ª©¤W¦U¦ì«e½ú¦n¡A§Ú¤w¸gºÉ¶qª¦¤å§ä¸ê®Æ¤F¡AÁÙ¬O¨S¸ê¤u¤H­ûªº¸£³UÂण¹L¨Ó¡A±o¨Ó³o½Ð±Ð¤j®a.....
¤p¤½¥q·Q°µ­Óexcel²³æªº·|­p¨t²Î¡A§Ú¦Û¤v¬ã¨sVBA¨ì¥Ø«e¤w§¹¦¨±o®t¤£¦h¡A°ß¿Wµn¿ý¶Ç²¼®É¡A¤£ª¾¹D«ç»ò¥s¹q¸£§PÂ_·í¤é¬O§_¤w¦³¨ä¥Lµn¿ýªºµ§¼Æ
½Ð±Ð±Ð§Ú¦p¦óÅýVBA¦Û°Ê§PÂ_µ§¼Æ¡A±qDATA¸ê®Æ®w¸Ì·j´M·í¤Ñ¬O§_¦³µn¿ý¶Ç²¼¹L¡A¨S¦³´N±q1¶}©l¡A¦pªG·í¤Ñ¦³¨ä¥Lµn¿ý¡A´N±q¶Ç²¼¸¹½X¸Ì¥½¨â½Xªºµ§¼Æ¬Ý¨ì²Ä´Xµ§¡A±q³o­Ó¼Æ¦r+1
¥Ø«e¼gªºµ{§Ç¥u¯à§PÂ_³Ì«á¤@µ§...­n¸Éµn´X¤Ñ«eªº¶Ç²¼´N¨S¿ìªk§PÂ_¤F....
javascript:;

javascript:;


'«Ø¥ß·í¤éµ§¼Æ
    Dim v As Integer    'Integer¾ã¼ÆÃþ«¬
    Dim Q As Integer
    v = Sheets("data").Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row  '³Ì«á¤@Äæ¬O²ÄvÄæ
    '¬YÄæ³Ì©³¦C  Cells(Rows.Count, Äæ).End(xlUp).Row
    'End(xlUp)¬°ctrl+¦V¤U
   Q = Sheets("data").Cells(v, "D")

    If Sheets("¶Ç²¼µn¿ý").Cells(5, "C") = Sheets("data").Cells(v, "D") Then '¤é´Á¦b¸ê®Æ®w¸Ì¦³­«½Æªº¸Ü
   
    '´N§â²{¦bªºµ§¼Æ¦A+1
    Sheets("¶Ç²¼µn¿ý").Cells(6, "C") = Sheets("¶Ç²¼µn¿ý").Cells(6, "C") + 1
   
    Else
    Sheets("¶Ç²¼µn¿ý").Cells(6, "C") = 1  '¨S¦³­«½Æªº¸Ü¡A¿é¤J1
    End If


ªí¹F¦p¦³¤£²M·¡Ãl¨¥Âئr³¡¤À½Ð¦h¥]²[¡A·PÁ¦U¦ì«e½ú¬Û§U~
³Ì«áªþ¤WÀÉ®×
javascript:;

¶Ç²¼µn¿ý¤¶­±¡AÂǥѶDz¼©ßÂà¨Ó¦Û°Ê²£¥Íµ§¼Æ¤Î¬y¤ô¸¹¡A¨Ã§â¸ê®Æ©ßÂà¨ìdata¤À­¶

2018-07-09_174314.png

±qdata¤À­¶§PÂ_¤é´Á¤Î¥Ø«eµ§¼Æ

2018-07-09_174320.png

°]·|¨t²Î½Ð±Ð.rar (123.61 KB)

¥»©«³Ì«á¥Ñ linyancheng ©ó 2018-7-10 11:56 ½s¿è

¦pªG¹ï·|­p¬yµ{¤£À´¡A«ÜÃø¤F¸Ñ±zªº·N«ä¡C

¬O§_¦b¸ê®Æ¸Ì¥[¤Wµn¿ý®É¶¡Äæ¡A³o¼Ë´N«Ü®e©ö§PÂ_¤F¡C
Word§e²{·¥­PªºÀRºA¤§¬ü¡AExcel®i²{·¥­Pªº°ÊºA¤§¬ü¡C

TOP

³o¬qµ{¦¡¥i¥H±N§A©Ò¿é¤Jªº¤é´Á
¦^¶Ç·s½sªº·í¤é¶Ç³æ½s¸¹¡C

ex: JudgmentNumber(1070501)="A107050106"

­ì¥»¦³¥´ºâÀ°§A§ïµ½ªí³æ¡A
¦ý¤£ª¾¹D§A§¹¾ãªº¥\¯à¹B§@¬O¤°»ò¤è¦V¡A
©Ò¥H´N¥ý´£¨Ñ³o­Ó¨ç¼Æµ¹§A¡I

§Æ±æ¯àÀ°ªº¨ì§A~ : )
  1. Function JudgmentNumber(iDate$) As String
  2.     Dim SNRng As Range, PT As Range
  3.     Dim iD&, iDMax&, iStart&
  4.     JudgmentNumber = ""
  5.     Set SNRng = Sheets("data").Columns(2)
  6.     Set PT = SNRng.Find("A" & iDate & "*", lookat:=xlWhole)
  7.     iStart = PT.Row
  8.     Do
  9.         iD = Mid(PT, 2, 9)
  10.         If iDMax < iD Then iDMax = iD
  11.         Set PT = SNRng.FindNext(PT)
  12.     Loop Until iStart = PT.Row
  13.     iD = iDMax + 1
  14.     JudgmentNumber = "A" & iD
  15. End Function
½Æ»s¥N½X

TOP

¦^´_ 2# linyancheng


   ±Ô­z¤£©ú«Ü©êºpOrz

TOP

¦^´_ 3# a5007185


    ·PÁ¤j¤jªº¼ö¤ß´£¨Ñ¡A«á¨Ó§ä¨ì¤è¦¡Åý¨t²Î¦Û¦æ§PÂ_data¥Ñ³Ì«á¤@µ§¸ê®Æºô¤W§ä¦P¤@¤Ñ¤é´Á¸Ì³Ì«á¤@µ§ªº¼Æ¦r¤F¡C
    ¨Ï¥Î¨ç¼Æloolup(2,1/½d³ò¡A´M§ä¹ï¶H¡A¶Ç¦^¦P¦Cªº¸ê®Æ½d³ò)

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD