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

[µo°Ý] (¤w¸Ñ¨M)°õ¦æµ{¦¡«á¸õ¥Xµøµ¡?

[µo°Ý] (¤w¸Ñ¨M)°õ¦æµ{¦¡«á¸õ¥Xµøµ¡?

¥»©«³Ì«á¥Ñ candy516 ©ó 2011-6-3 18:22 ½s¿è

¦U¦ì«e½ú±z¦n~
§Ú¥Î¤F¤§«eHsieh«e½úÀ°§Ú¼gªºµ{¦¡¿z¿ï¸ê®Æ¡A¤§«e³£ÁÙ¥i¥H¥Î!
¦ý²{¦b¥Î³£·|¸õ¥X¤@¨Ç©_©ÇªºªF¦è¡A½Ð°Ý¦³¨S¦³¤H¥i¥HÀ°§Ú¸Ñµª¤@¤U©O?¥ýÁÂÁ¦U¦ì«e½ú!^^
(¦]ÀɮפӤj¡A§Ú¥ý±N01~09¦~¸ê®Æ§R°£¤F¡A¥u³Ñ2010¦~!)

µ{¦¡½X¦p¤U
  1. Sub ex()
  2. On Error Resume Next
  3. Dim A As Range, B As Range, B1 As Range, B2 As Range, C As Range, Rng As Range, Rng1 As Range, d As Object
  4. Set d = CreateObject("Scripting.Dictionary")
  5. Set sht = Sheets.Add(after:=Sheets(1))
  6. Application.ScreenUpdating = False
  7. With Sheet1
  8.    For Each A In .Range(.[A2], .[A65536].End(xlUp))
  9.       mystr = A & "," & Left(A.Offset(, 1), 4)
  10.      '§ASheet1ªºAÄæ¬O¥H¤é´Á®æ¦¡yyyy/m/d¿é¤J¡A¦ý®æ¦¡³]¦¨yyyymmdd¡A©Ò¥H¡A³y¦¨«D¥þ³¡¬°8½X
  11.    '¥ÎTEXTÄݩʱo¨ì©Ò¨£¦r¦ê
  12.       d(mystr) = DateValue(Format(A.Offset(, 1).Text, "0000/00/00"))
  13.       If Err.Number <> 0 Then MsgBox A & A.Offset(, 1)
  14.    Next
  15. End With
  16. k = 1: r = 1
  17. For Each ky In d.keys
  18. y = Split(ky, ",")(1)
  19. With Sheets(y)
  20. Set C = .Columns("A").Find(d(ky))
  21. Set B = .Rows(1).Find(Split(ky, ",")(0))
  22. If Not C Is Nothing And Not B Is Nothing Then
  23. x = Application.Max(3, C.Row - 14)
  24.    Set Rng = .Cells(x, 1).Resize(15, 1)
  25.    Set Rng1 = .Cells(x, B.Column).Resize(15, 1)
  26.    With sht
  27.       Rng.Copy .Cells(r, k)
  28.       Rng1.Copy .Cells(r, k + 1)
  29.       .Cells(r, 3) = y & "¦~²Ä" & B.Column - 1 & "µ§"
  30.    End With
  31.    r = r + 15
  32.    Else
  33.    MsgBox "µL¦¹°£Åv¸ê®Æ"
  34. End If
  35. End With
  36. Next
  37. Application.ScreenUpdating = True
  38. End Sub
½Æ»s¥N½X

TEST ²{ª÷ªÑ§Q.rar (864.86 KB)

¦^´_ 1# candy516

§A¦³°Ê¨ì¤@¨Çµ{¦¡½X¶Ü???
¦]¬°´N§Ú­è­è§â§Aªºµ{¦¡½X¥á¥h°®²bEXCELÀɮפº ´Nµo²{ ¦n¦h¬õ¦r...
           mystr = A "," & Left(A.Offset(, 1), 4)
      If Err.Number <> 0 Then MsgBox A A.Offset(, 1)
      .Cells(r, 3) = y "¦~²Ä" & B.Column - 1 & "µ§"


¬õ³q³q..

TOP

¦^´_ 2# mark15jill


    §A¦n~
§Ú­«¶K¤@¦¸µ{¦¡½XÅo!^^

TOP

¦U¦ì«e½ú¡A§Ú§ä¥X°ÝÃD¤F!= =
¬O¦³¤@¦~¬Y¤@µ§¸ê®Æ¦³»~¡A©Ò¥H¾É­P«á­±³£¶]¤£¥X¨Ó!
ÁÂÁ§A­Ì­ò!^^

TOP

        ÀR«ä¦Û¦b : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD