[µo°Ý] ¸ê®Æ®Mªí

PJChen

¤j¤j¦n,

¶ìªO¤u§@ªí­n®M¥Î¸ê®Æ,½Ð°Ýµ{¦¡«ç»ò¼g? ¸ê®Æ®Mªí.rar (21.47 KB)

¤U¸ü¦¸¼Æ: 9

2021-9-28 17:30

"¹w¬ù"¤u§@ªí¸ê®Æ·|¤@ª½¼W¥[,
"¹w¬ù"¤u§@ªíOÄæ="V",«h
1. ¤é´Á¡G¶ìªOªºC17="¹w¬ù"AÄæ
2. ¼Æ¶q¡G¶ìªOªºE22 ="¹w¬ù"CÄæ
3. «È¤á¡G¶ìªOªºC16 = »¡©úK:N¨ú¬Û¦P«È¤á¸ê®Æ
4. ©Ó¿ì¡G¶ìªOªºC26 = »¡©úK:N¨ú¬Û¦P«È¤á¸ê®Æ
5. ¹q¸Ü¡G¶ìªOªºC27 = »¡©úK:N¨ú¬Û¦P«È¤á¸ê®Æ
6. ¶ñªí¤é¡G¶ìªOªºC28 = »¡©úK:N¨ú¬Û¦P«È¤á¸ê®Æ

§¹¦¨«á±N¶ìªO¤u§@ªí³æ¿W
¥t¦s¸ô®| D:\¨Î-20210929 (¤é´ÁYYYYMMDD,»P1.¬Û¦P)

Ex: "¹w¬ù"¤u§@ªíO6="V"=¨Î
«h¶ìªO¤u§@ªí¶ñ¤J
1. ¤é´Á¡G¶ìªOªºC17=2021/9/29
2. ¼Æ¶q¡G¶ìªOªºE22 =120
3. «È¤á¡G¶ìªOªºC16 = ¼È¥N4
4. ©Ó¿ì¡G¶ìªOªºC26 = ¥NºÙ4
5. ¹q¸Ü¡G¶ìªOªºC27 = ¹q¸Ü4
6. ¶ñªí¤é¡G¶ìªOªºC28 = ·í¤Ñ¤é´Á=2021/9/28

samwang

¦^´_ 1# PJChen

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, Brr, i&, C%
Arr = Range([¹w¬ù!o1], [¹w¬ù!a65536].End(3))
Brr = Sheets("»¡©ú").[j1].CurrentRegion
Tm = Timer
For i = 2 To UBound(Arr)
    If UCase(Arr(i, 15)) = "V" Then
        With Sheets("¶ìªO")
            .[c17] = Arr(i, 1): .[e22] = Arr(i, 3): .[c28] = Date
            For j = 2 To UBound(Brr)
                If Arr(i, 2) = Brr(1, j) Then
                .[c16] = Brr(2, j): .[c26] = Brr(3, j): .[c27] = Brr(4, j)
                End If
            Next
        End With
        C = 1: Exit For
    End If
Next
If C = 1 Then
    Application.DisplayAlerts = False
    Sheets("¶ìªO").Copy
    ActiveWorkbook.SaveAs "D:\" & "¨Î-" & Format(Date, "yyyymmdd") & ".xlsx"
    ActiveWindow.Close
    Application.DisplayAlerts = True
    With Sheets("¶ìªO")
        .[c17] = "": .[e22] = "": .[c28] = ""
        .[c16] = "": .[c26] = "": .[c27] = ""
    End With
End If
MsgBox Timer - Tm
End Sub

PJChen

¦^´_ 2# samwang
±z¦n,
µ{¦¡¦³ÂI¤p­×§ï,¤£ª¾¬°¦ó,®M¥Î¦b"¨Î",¸ê®Æ´N¤£§¹¾ã,
¤w¸g¬d§ä¤F¤@­Ó¤p®É,ÁÙ¬O§ä¤£¨ì­ì¦],¥i§_À°¦£¬Ý¤U¡H
½Ð¥Î§Ú­×§ï¹Lªº¸ê®Æ¸Õ¸Õ.... ¸ê®Æ®Mªí.rar (21.33 KB)

¤U¸ü¦¸¼Æ: 5

2021-9-30 01:29

samwang

¦^´_ 3# PJChen


¤£¦n·N«ä¡A§Ú´ú¸Õ¨S°ÝÃD¡A¥i§_½Ð¦b©ú½T»¡©ú±zªº°ÝÃD­þ¸Ì?
ÁÂÁÂ

PJChen

¦^´_ 4# samwang
±z¦n,
§Ú¥Î¤W¶Ç³o­ÓÀÉ,§â¦³°ÝÃDªº®Mªí¸ê®Æ¯d¦b¨º,½ÐÀ°§Ú¬Ý¤U ¸ê®Æ®Mªí.rar (21.38 KB)

¤U¸ü¦¸¼Æ: 3

2021-9-30 20:24

§Ú¤ÏÂдú¸Õ, ±N¸ê®Æ°µ½Õ´«,µo²{"»¡©ú"¤u§@ªí¤¤ªºOÄæ«áªº¸ê®Æ±a¤£¥X¨Ó¡I
¦pªG§Arunªºµ²ªG¬O¨S°ÝÃDªº,¨º¨ì©³­ì¦]¬°¦ó?¦³ªk§ïµ½¶Ü?

samwang

¦^´_ 5# PJChen

¨º¨ì©³­ì¦]¬°¦ó?¦³ªk§ïµ½¶Ü?
>> ¤£¦n·N«ä¡A³y¦¨±zªº§xÂZ¡A§Úªº²Ê¤ß¤j·N¡A¤w§ó·s¦pªþ¥ó¡AÁÂÁÂ
For j = 2 To UBound(Brr, 2)
(Brr,2)¾î¦V(Äæcol)§ä¸ê®Æ
(Brr)ª½¦V(¦Crow)§ä¸ê®Æ

PJChen

¦^´_ 6# samwang

For j = 2 To UBound(Brr, 2)
(Brr,2)¾î¦V(Äæcol)§ä¸ê®Æ
(Brr)ª½¦V(¦Crow)§ä¸ê®Æ

°õ¦æ¨S°ÝÃD¤F,«D±`·PÁ»¡©ú¡I

PJChen

¦^´_ 6# samwang
Sam, ±z¦n
½Ð°Ý...§Ú·QÅý®Mªí°Ê§@¥i¥H¤@¦¸³sÄò§¹¦¨¦h±iªí®æ,
OÄ椣¤@©w¬°³sÄò"V"
¦p¦óÅý©Ò¦³"V"
³sÄò°µ®Mªí°Ê§@,ª½¨ì§¹¦¨¡H
¸ê®Æ®Mªí.rar (19.61 KB)

¤U¸ü¦¸¼Æ: 7

2021-12-5 08:41

samwang

¦^´_ 8# PJChen

¦p¦óÅý©Ò¦³"V"
³sÄò°µ®Mªí°Ê§@,ª½¨ì§¹¦¨¡H
>> ½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, Brr, i&, C%
Tm = Timer
Application.DisplayAlerts = False
Brr = Sheets("»¡©ú").[j1].CurrentRegion
Arr = Range([¹w¬ù!o1], [¹w¬ù!a65536].End(3))
With Sheets("¶ìªO")
    .[c17] = "": .[e22] = "": .[c28] = ""
    .[c16] = "": .[c26] = "": .[c27] = ""
End With
For i = 2 To UBound(Arr)
    If UCase(Arr(i, 15)) = "V" Then
        With Sheets("¶ìªO")
            .[c17] = Arr(i, 1): .[e22] = Arr(i, 3): .[c28] = Date 'Arr(i, 1)=¤é´Á,Arr(i, 3)=¼Æ¶q
            For j = 2 To UBound(Brr, 2)
                If Arr(i, 2) = Brr(1, j) Then
                .[c16] = Brr(2, j): .[c26] = Brr(3, j): .[c27] = Brr(4, j)
                End If
            Next
        End With
        Sheets("¶ìªO").Copy
        ActiveWorkbook.SaveAs "D:\" & Arr(i, 2) & "-" & Format(Arr(i, 1), "yyyymmdd") & ".xlsx"
        ActiveWindow.Close
        With Sheets("¶ìªO")
            .[c17] = "": .[e22] = "": .[c28] = ""
            .[c16] = "": .[c26] = "": .[c27] = ""
        End With
    End If
Next
MsgBox Timer - Tm
Application.DisplayAlerts = True
End Sub