¡@¼ÊºÙ: 温©_ ¡@ÀY»Î: ij½×ªÌ
¤¤¾Ç¥Í
- ©«¤l
- 104
- ¥DÃD
- 5
- ºëµØ
- 0
- ¿n¤À
- 110
- ÂI¦W
- 0
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- Office2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2017-11-14
- ³Ì«áµn¿ý
- 2019-4-30
|
¦^´_ 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- Option Explicit
- Sub ex()
- Dim S As Variant, Wb As Workbook, Wb_Name As String
- Dim n, m As Long
- Application.ScreenUpdating = False
- Set Wb = Workbooks.Open("\\TW100019913\Access\B.xlsx")
- For n = 2 To Application.CountA(Workbooks("A.xlsm").Sheets("Form").Range("A:A"))
- S = Application.WorksheetFunction.Text(Workbooks("A.xlsm").Sheets("Form").Cells(n, 3) - Workbooks("A.xlsm").Sheets("Form").Cells(n, 2), "dd hh:mm:ss")
- Workbooks("A.xlsm").Sheets("Form").Cells(n, 5) = S
- For m = 2 To Application.CountA(Wb.Sheets("Time").Range("A:A"))
- If Workbooks("A.xlsm").Sheets("Form").Cells(n, 1) = Wb.Sheets("Time").Cells(m, 1) Then '§PÂ_B.xlsx¦³¨S¦³¤Hû¸ê®Æ
- If S >= Application.WorksheetFunction.Text(Wb.Sheets("Time").Cells(m, 2), "dd hh:mm:ss") Then '¤ñ¹ï¤Hû¥»¦¸¹B°Ê®É¶¡¬O§_¹F¨ì¼Ð·Ç
- Workbooks("A.xlsm").Sheets("Form").Cells(n, 4) = "¨¬°÷"
- Exit For
- Else
- Workbooks("A.xlsm").Sheets("Form").Cells(n, 4) = "¤£¨¬"
- Exit For
- End If
- Else
- Workbooks("A.xlsm").Sheets("Form").Cells(n, 4) = "¨S·|û"
- End If
- Next
- Next
- Wb.Sheets("Time").Parent.Close False
- End Sub
½Æ»s¥N½X ªþ¤WÀÉ®× |
-
-
Access.rar
(25.1 KB)
|