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

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

¦^´_ 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

¦^´_ 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

¥»©«³Ì«á¥Ñ 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

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD