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

¸ê®ÆÂà¼g+VBA¤ë¥÷¬y¤ô¸¹

¸ê®ÆÂà¼g+VBA¤ë¥÷¬y¤ô¸¹

¥»©«³Ì«á¥Ñ man65boy ©ó 2012-5-5 21:23 ½s¿è

¦p¦ó¦bÀɮתºAÄæ¸Ì³]¸mVBA¦Û°Ê§PŪªº¬y¤ô¸¹,¤p§Ì¦³¸Õ¹L¨ç¼ÆÀ³¥Î¡A¦ý¡A¦]¬°¤Ä¿ï"¦Û¨ú"«á¡AÂà¼g¨ìSheet2®É¡A¨ç¼Æ·|­«·s¦A½s¸¹¡A³o¼Ë·|¨Ï¸ê®ÆµLªkÅܦ¨·íªì©Ò½sªº½s½X¡C
½Ð±Ð¦U¦ì¥ý¶i¦Ñ®v­Ì¡A¦p¦óÅýAÄ檺¬y¤ô¸¹¡A¦bÂà¼g«á¡A¨Ì«O«ùµÛ­ì¨Óªº½s½X¡AÁÂÁÂ!
¸ê®ÆÂà¼g¥[¬y¤ô¸¹.rar (11.25 KB)

¥»©«³Ì«á¥Ñ play9091 ©ó 2012-5-5 22:18 ½s¿è

¥i¥H¸Õ¸Õ¬Ý¶K¨ì"Sheet2"¤W­±ªº®É­Ô¡A¥Î"¶K¤W­È"¡A³o¼Ë¤l´N¤£·|¶]±¼¤F¡I
  1.   If Not Rng Is Nothing Then
  2.      Rng.Copy
  3.      Sheet2.[A65536].End(xlUp).Offset(1).PasteSpecial xlPasteValues
  4.      Rng.EntireRow.Delete
  5.   End If
½Æ»s¥N½X
ªì¾ÇVBA¥E¨ìtwbts

TOP

¦^´_ 2# play9091


    ÁÂÁ¤j¤jªº­×§ï«Øij¡A¦ý¬O¡A ¤p§Ì¦bAÄæ¨Ï¥Îªº¨ç¼Æ=TEXT(B2,"mm")&"-"&TEXT(SUMPRODUCT((TEXT($B$1:$B2,"yymm")=TEXT(B2,"yymm"))*1),"000")¡A¸ê®Æ¤£Âà¼g¨«ªº¸Ü¡A¨S°ÝÃDªº¡A
  ¥i¬O SHEET1ªºAÄæ¨ç¼Æ¬O·|ÀHµÛÂà¼g¨«ªº¸ê®ÆÅÜ°Ê­«±Æ¡A©Ò¥H¤p§Ì§Æ±æ¯à¦bAÄæ¨Ï¥ÎVBA¬y¤ô¸¹¡A

TOP

Sub yy()
    Dim b(12) As Byte, c As Range
    For Each c In Range([b2], [b2].End(4))
        b(Month(c)) = b(Month(c)) + 1
        c(1, 0).Value = Format(Month(c), "00") & "-" & Format(b(Month(c)), "000")
    Next
End Sub

TOP

¦^´_ 4# oobird


    ÁÂÁÂoobird¤j¤jªº¦^µª¡AÂà¼g¨ìsheet2ªº¸ê®Æ¥¿½TµL»~¡A¦ý¡Asheet1ªº¸ê®Æ¥u­n¦b«ö¶s¤@¤U¡A´N­«·s±Æ¦C¤F¡A³o¼Ë·|¨Ïsheet1ªº½s¸¹¸ê®Æ¸òsheet2½s¸¹¸ê®Æ­«½Æ¡A
¤p§Ì·Q±o¤£°÷¦h¡A»¡ªº¤£²M·¡¡A©êºp!
   ¬y¤ô¸¹­«·s»¡©ú:sheet1ªºA Äæ½s¸¹¬°2012-05-001<<¥[¤W¦~¥÷(³o¼Ë¤ñ¸û¹³°ß¤@½X)¡A¤@¼ËÂà¼g«á¡A¦b«ö¶s½s½X®É¡A¤£¯à©Msheet2½s¸¹¸ê®Æ­«½Æ¡A²³æ¨Ó»¡¡AÂà¼g2012-05-002¨ìsheet2®É¡A
sheet1AÄæ¸Ì¤£¯à¦b¦³¬Û¦Pªºsheet2AÄ檺¬y¤ô¸¹¡A³o¼Ë¤~¯à«O¯d·íªì³]©wªº½X¸¹¬O°ß¤@¡A³Â·Ð¦U¦ì¦Ñ®vÀ°¦£!


¸ê®ÆÂà¼g¥[¬y¤ô¸¹2.rar (11.46 KB)

TOP

¥»©«³Ì«á¥Ñ register313 ©ó 2012-5-8 08:57 ½s¿è

¦^´_ 5# man65boy
  1. Sub yy()
  2. '«Ø¥ß¦r¨åª«¥ó
  3. Set d = CreateObject("scripting.dictionary")
  4. '¦bSheet2¤u§@ªíb2Àx¦s®æ~bÄæ³Ì«á¤@®æ§@°j°é
  5. For Each c In Sheet2.Range(Sheet2.[b2], Sheet2.[b2].End(4))
  6.   '­YbÄæÀx¦s®æ¤§¤º®e¬°ªÅ­È«hÂ÷¶}°j°é
  7.   If c = "" Then Exit For
  8.   '­pºâ¦P¦~¦P¤ë¤À§O¥X²{¤§¦¸¼Æ
  9.   d(Year(c) & Month(c)) = d(Year(c) & Month(c)) + 1
  10. 'ªð¦^°j°é
  11. Next
  12. '¦bSheet1¤u§@ªíb2Àx¦s®æ~bÄæ³Ì«á¤@®æ§@°j°é
  13. For Each c In Sheet1.Range(Sheet1.[b2], Sheet1.[b2].End(4))
  14.   '­pºâSheet1¤u§@ªí¤¤¦P¦~¦P¤ë¤À§O¥X²{¤§¦¸¼Æ(§t¤§«eSheet2¤u§@ªí)
  15.   d(Year(c) & Month(c)) = d(Year(c) & Month(c)) + 1
  16.   '­YbÄæÀx¦s®æ¤§¤º®e¬°ªÅ­È«h aÄæÀx¦s®æ¤§¤º®e=bÄ椧 ¦~-¤ë-¬y¤ô¸¹
  17.   If c(1, 0).Value = "" Then c(1, 0).Value = Format(c, "yyyy-mm") & "-" & Format(d(Year(c) & Month(c)), "000")
  18. 'ªð¦^°j°é
  19. Next
  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 6# register313


    ·PÁÂregister313¤j¤j§¹¬üªº¸Ñµª¡A¥i§_½Ð¤j¤j¦bµ{¦¡½X¤¤³Æµù»yªk¡A¦nÅý¤p§Ì¾Ç²ß¡A¦A¦¸ªº·PÁ§A¡C

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, Y, i&, j%, T$, xR As Range, Sh
Set Y = CreateObject("Scripting.Dictionary")
Sh = Array(, Sheets("sheet2"), Sheets("sheet1"))
For j = 1 To 2
   Set xR = Range(Sh(j).[A1], Sh(j).Cells(Rows.Count, "F").End(3))
   Brr = xR
   For i = 2 To UBound(Brr)
      T = Format(Brr(i, 2), "YYYY-MM-")
      Y(T) = Y(T) + 1
      Brr(i, 1) = T & Format(Y(T), "000")
   Next
   If j = 2 Then
      Intersect(xR.Offset(1, 0), [A:A]).ClearContents
      xR = Brr
   End If
Next
Set Y = Nothing: Set xR = Nothing: Set Sh = Nothing: Erase Brr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : §Ú­Ì³Ì¤jªº¼Ä¤H¤£¬O§O¤H¡D¥i¯à¬O¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD