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

[µo°Ý] ¶}±Ò2­ÓExcel°µ¸ê®Æ¤ñ¹ï

[µo°Ý] ¶}±Ò2­ÓExcel°µ¸ê®Æ¤ñ¹ï

½Ð°Ý¦³½Ö¥i¥H±Ð§Ú¶Ü...
­è­è¬Ý¤F¤@½g[µo°Ý] ½Ð°ÝVBA¥i¥H°µ¨ì¨âÀɮפñ¹ï«á¦A²£¥Í¥t¤@Àɮתº¤ñ¹ïµ²ªG¶Ü?
¬Ý¤F¦n¤[ÁÙ¬O¬Ý¤£À´...
Àµ½Ð°ª¤â«ü¾É
§Ú­n¦p¦ó¥ÎExcel A¥h¤ñ¹ïExcel Bªº¸ê®Æ¦A¦^¶ÇExcel A«ü©wªºÄæ¦ì?
°²³]¤ý¤j³°¶i¤J®É¶¡18:00Â÷¶}18:30¡A¬Û´î«á¹B°ÊÁ`®É¼Æ¬°30¤ÀÄÁ¡A
µM«á¤ñ¹ïExcel B¥LÀ³¸Ó¨C¦¸¹B°ÊÀ³­nº¡2¤p®É¡A
©Ò¥H²Å¦X¹B°Ê®É¶¡­n¦^¶ñ¤£²Å¦X¡C
³¯·N²[¶i¤J®É¶¡18:00Â÷¶}21:20¡A¬Û´î«á2¤p®É20¤ÀÄÁ¡A
²Å¦X¹B°Ê®É¶¡­n¦^¶ñ²Å¦X¡C

¦³«ü©wªº¬¡­¶¦WºÙ
Excel "A.xlsx" (Sheets("Form"))
©m¦W        °·¨­©Ð¶i¤J®É¶¡        °·¨­©ÐÂ÷¶}®É¶¡        ²Å¦X¹B°Ê®É¶¡
¤ý¤j³°        2018-07-01 18:00:00        2018-07-01 18:30:00       
³¯·N²[        2018-07-02 18:00:00        2018-07-02 21:20:00       
½²¨ÌªL        2018-07-01 08:00:00        2018-07-01 09:00:00       
ù§Ó²»        2018-07-03 16:00:00        2018-07-03 18:00:00       
¥ú¨}        2018-07-04 18:00:00        2018-07-04 20:00:00       
±i´É¼Ù        2018-07-04 16:00:00        2018-07-04 20:00:00       

Excel "B.xlsx" (Sheets("Time"))
©m¦W        ¹B°Ê®É¶¡
¤ý¤j³°        02:00:00
³¯·N²[        02:00:00
¥ú¨}        03:00:00
½²¨ÌªL        01:00:00
ù§Ó²»        01:00:00
±i´É¼Ù        02:00:00
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

½Ð°Ýª©¥D¦³Â²µuªº¼gªk¶Ü?
  1. Sub ex()
  2. Dim f1, f2, f5 As Workbook
  3. Set f1 = Sheets("Form")
  4. Set f2 = Sheets("Time")
  5. For Each aa In Range([A2], [A2].End(xlDown))
  6. a = Application.WorksheetFunction.Text(aa.Offset(0, 2) - aa.Offset(0, 1), "dd hh:mm:ss")
  7. Set Wb = Workbooks.Open("\\Test\B.xlsx")
  8.     For m = 2 To Application.CountA(Wb.Sheets("Time").Range("A:A"))
  9.         If Workbooks("A.xlsm").f1.Offset(, 1) = Wb.Sheets("Time").Cells(m, 1) Then
  10.             If a >= Application.WorksheetFunction.Text(Wb.Sheets("Time").Cells(m, 2), "dd hh:mm:ss") Then
  11.             Workbooks("A.xlsm").f1.Offset(, 3) = "¨¬°÷"
  12.             Exit For
  13.             Else
  14.             Workbooks("A.xlsm").f1.Offset(, 3) = "¤£¨¬"
  15.             Exit For
  16.             End If
  17.         End If
  18.     Next
  19. Wb.Sheets("AgingUpData").Parent.Close False
  20. End Sub
½Æ»s¥N½X
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 2# faye59
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range, D As Object, E As Range
  4.     With Sheets("Form")
  5.         Set Rng = .Range(.[d2], .Range("d" & .[a2].End(xlDown).Row))  '²Å¦X¹B°Ê®É¶¡ªº¦C¦ì
  6.     End With
  7.     Rng = "=IF(rc[-1]-rc[-2]>=TIMEVALUE(""02:00:00""),""²Å¦X"",""¤£²Å¦X"")"  '¼g¤W¤½¦¡ (°·¨­©ÐÂ÷¶}®É¶¡-°·¨­©Ð¶i¤J®É¶¡>=02:00:00)
  8.     Rng.Value = Rng.Value  '¤½¦¡Âର­È
  9.     Set D = CreateObject("Scripting.Dictionary")   '¦r¨åª«¥ó
  10.     For Each E In Rng       'Rng.Cells ªº°j°é
  11.         D(E.Offset(, -3).Text) = E.Text  'ÃöÁä¦r-> E.Offset(, -3)¬°©m¦W±a¤J (²Å¦X/¤£²Å¦X)
  12.     Next
  13.     '­«³]RngÅܼÆ
  14.     Set Rng = Workbooks.Open("\\Test\B.xlsx").Sheets("Time").Range("A:A").SpecialCells(xlCellTypeConstants)
  15.    
  16.     For Each E In Rng
  17.         If D.EXISTS(E.Text) Then E.Offset(, 1) = D(E.Text)  'E¬°AÄæ -> E.Offset(, 1)= BÄæ
  18.         '¦r¨åª«¥óªºExists ¤èªk :¦r¨åª«¥óªºÃöÁä¦r¦s¦b®É¬°TTrue, °õ¦æ(Then  .... )
  19.     Next
  20.     Rng.Parent.Parent.Close False
  21.     'Rng.Parent ¬OSheets("Time")
  22.     'Rng.Parent.Parent¬°Workbook
  23. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# GBKEE


    Option Explicit
Sub Ex()
    Dim Rng As Range, D As Object, E As Range
    With Sheets("Form")
        Set Rng = .Range(.[d2], .Range("d" & .[a2].End(xlDown).Row))  '²Å¦X¹B°Ê®É¶¡ªº¦C¦ì
    End With
    Rng = "=IF(rc[-1]-rc[-2]>=TIMEVALUE(""02:00:00""),""²Å¦X"",""¤£²Å¦X"")"  '¼g¤W¤½¦¡ (°·¨­©ÐÂ÷¶}®É¶¡-°·¨­©Ð¶i¤J®É¶¡>=02:00:00)
    Rng.Value = Rng.Value  '¤½¦¡Âର­È
    Set D = CreateObject("Scripting.Dictionary")   '¦r¨åª«¥ó
    For Each E In Rng       'Rng.Cells ªº°j°é
        D(E.Offset(, -3).Text) = E.Text  'ÃöÁä¦r-> E.Offset(, -3)¬°©m¦W±a¤J (²Å¦X/¤£²Å¦X)
    Next
    '­«³]RngÅܼÆ
    Set Rng = Workbooks.Open("\\Test\B.xlsx").Sheets("Time").Range("A:A").SpecialCells(xlCellTypeConstants)
   
    For Each E In Rng
        If D.EXISTS(E.Text) Then E.Offset(, 1) = D(E.Text)  'E¬°AÄæ -> E.Offset(, 1)= BÄæ
        '¦r¨åª«¥óªºExists ¤èªk :¦r¨åª«¥óªºÃöÁä¦r¦s¦b®É¬°TTrue, °õ¦æ(Then  .... )
    Next
    Rng.Parent.Parent.Close False
    'Rng.Parent ¬OSheets("Time")
    'Rng.Parent.Parent¬°Workbook
End Sub


GBKEE¶WªO
¤£¦n·N«ä¡A§Ú¦³­×¥¿¤@¨Ç§PÂ_¦¡¡A
»Ý­n¤ñ¹ïBÀɮפ¤ªº¹B°Ê®É¶¡¸ê®Æ¬O§_²Å¦X¡A
·í¨S¦³³o­Ó¤H­û®É¦^¶Ç"¨S·|­û"¡A
¨Ã¦bAÀɮפ¤EÄ楼¶ñ¤J¸Ó­û¥»¦¸¹B°Ê®É¶¡¡C
  1. Option Explicit
  2. Sub ex()
  3.     Dim S  As Variant, Wb As Workbook, Wb_Name As String
  4.     Dim n, m As Long
  5.     Application.ScreenUpdating = False
  6.     Set Wb = Workbooks.Open("\\TW100019913\Access\B.xlsx")
  7.         For n = 2 To Application.CountA(Workbooks("A.xlsm").Sheets("Form").Range("A:A"))
  8.         S = Application.WorksheetFunction.Text(Workbooks("A.xlsm").Sheets("Form").Cells(n, 3) - Workbooks("A.xlsm").Sheets("Form").Cells(n, 2), "dd hh:mm:ss")
  9.         Workbooks("A.xlsm").Sheets("Form").Cells(n, 5) = S
  10.             For m = 2 To Application.CountA(Wb.Sheets("Time").Range("A:A"))
  11.             If Workbooks("A.xlsm").Sheets("Form").Cells(n, 1) = Wb.Sheets("Time").Cells(m, 1) Then '§PÂ_B.xlsx¦³¨S¦³¤H­û¸ê®Æ
  12.                 If S >= Application.WorksheetFunction.Text(Wb.Sheets("Time").Cells(m, 2), "dd hh:mm:ss") Then '¤ñ¹ï¤H­û¥»¦¸¹B°Ê®É¶¡¬O§_¹F¨ì¼Ð·Ç
  13.                     Workbooks("A.xlsm").Sheets("Form").Cells(n, 4) = "¨¬°÷"
  14.                     Exit For
  15.                 Else
  16.                     Workbooks("A.xlsm").Sheets("Form").Cells(n, 4) = "¤£¨¬"
  17.                     Exit For
  18.                 End If
  19.             Else
  20.                 Workbooks("A.xlsm").Sheets("Form").Cells(n, 4) = "¨S·|­û"
  21.             End If
  22.             Next
  23.         Next
  24.     Wb.Sheets("Time").Parent.Close False
  25. End Sub
½Æ»s¥N½X
ªþ¤WÀÉ®×

Access.rar (25.1 KB)

¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 4# faye59
  1. Option Explicit
  2. Sub Ex()
  3.     Dim  D As Object, i As Integer , xTime As Date
  4.     Set D = CreateObject("Scripting.Dictionary")   '¦r¨åª«¥ó
  5.     With Workbooks.Open("\\Tank\Access\B.xlsx").Sheets("Time").Range("A:A")
  6.         i = 2
  7.         Do While .Cells(i) <> ""
  8.         If Not IsDate(.Cells(i, "B")) Then MsgBox .Cells(i) & " - " & .Cells(i, "B") & " ¤£¬O¥¿½T®É¶¡ ": End
  9.             D(.Cells(i).Text) = Application.Text(.Cells(i, "B"), "HH:MM")
  10.             i = i + 1
  11.         Loop
  12.     End With
  13.     With Sheets("Form").Range("A:A")
  14.         i = 2
  15.         Do While .Cells(i) <> ""
  16.             If D.EXISTS(.Cells(i).Text) Then
  17.                 .Cells(i, "E") = D(.Cells(i).Text)
  18.                 xTime = Application.Text(.Cells(i, "C") - .Cells(i, "B"), "HH:MM")
  19.                 If xTime >= D(.Cells(i).Text) Then
  20.                     .Cells(i, "D") = "¨¬°÷"
  21.                 Else
  22.                     .Cells(i, "D") = "¤£¨¬°÷"
  23.                 End If
  24.             Else
  25.                 .Cells(i, "d") = "¨S·|­û"
  26.             End If
  27.             i = i + 1
  28.         Loop
  29.     End With
  30. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE
  1.     If Not IsDate(.Cells(i, "B")) Then MsgBox .Cells(i) & " - " & .Cells(i, "B") & " ¤£¬O¥¿½T®É¶¡ ": End
½Æ»s¥N½X
§Ú¤@ª½¸õ¥X³o¬qMsgbox°T®§
¶WªOªº·N«ä¬O¥ý±NB.xlsx¤º©Ò¦³¸ê®Æ«Ø¦¨¦r¨å¶Ü?
¦A±N¦r¨å®M¤JA.xlsm¶ñ¼g¸ê°T?
¥i¬O¬°¤°»ò­nIf Not IsDate
¤£¬O«Ü©ú¥Õ³o¬q·N«ä...
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2018-8-9 08:11 ½s¿è

¦^´_ 6# faye59
If Not IsDate(.Cells(i, "B"))      **Àˬd  .Cells(i, "B")¬O§_¬° ¦³®Ä¤é´Á**
'**¦pªG¹Bºâ¦¡¬O¤@­Ó¤é´Á¡A©Î¬O¹³¦³®Ä¤é´Á¤@¼Ë¥iÃѧOªº¡AIsDate ·|¶Ç¦^True¡F§_«h¥¦·|¶Ç¦^ False**

¬O¦bÀˬdB.xlsxªº¹B°Ê®É¶¡¬O§_¿é¤J¥¿½Tªº®É¶¡¼Æ­È
B.xlsxªº¤u§@ªíTIME, C2=ISNUMBER(B2) ¶Ç¦^False  **¤£¬O¼Æ­È
B.xlsxªº¤u§@ªíTIME, C2=ISTEXT(B2)        ¶Ç¦^True  **¬O¤å¦r

B.xlsxªº¤u§@ªíTIME, C3=ISNUMBER(B3)  ¶Ç¦^True  **¬O¼Æ­È
B.xlsxªº¤u§@ªíTIME, C3=ISTEXT(B3)          ¶Ç¦^False  **¤£¬O¤å¦r
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE


    ©ú¥Õ¤F...­ì¨Ó¬O§ÚB.xlsxÀÉ®×Àx¦s®æ¤º¿é¤J¤å¦r¡A
ÁÙ·Q»¡§Ú³£¤w¸g³]©w¦¨¤é´Á¬°¦óÁÙ­nNot IsDate...
BÀɮקڬO¥ÎVB.net³]©w¶ñ¤W¸ê®Æ¡A·íªì²Ä¤@µ§¬O¤â°Ê¦b¶ñ¼g¤~¶ñ¿ù¤F§a!
³o²Ó¸`ÁÙ¯u¨Sª`·N¨ì¡A°ÝÃD¤w¸Ñ¨M¡C
ÁÂÁÂGBKEE¶WªO¦^ÂÐ!
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD