lpk187

¦^´_ 1# missbb

Range(fr, fc)¤¤ªºfr¡Bfc¥²»Ý¬°Address¤£¬O¥ÎÄ渹©M¦C¸¹
©Ò¥H­n§ï¦¨Cells(fr, fc)

lpk187

¦^´_ 3# missbb


   
Sub match2()
'combine id card no and date for searching


Dim srcrange As Range, fndrange As Range
Dim fstaddress As String, i As Integer
Dim fc As Integer
Dim fr As Integer
Dim fr2 As Integer

Worksheets("attendance report").Activate

fc = 1
fr = 4
fr2 = 3

Set srcrange = Worksheets("data").Range("a4").CurrentRegion.Columns(9)
Set fndrange = srcrange.Find(what:=Cells(fr, fc) & Format(Cells(fr2, 11), "d/m/yyyy"))
If Not fndrange Is Nothing Then

fstaddress = fndrange.Address

i = 5
Do
Cells(i, 11).Value = fndrange.Offset(, -2).Value
Cells(i + 1, 11).Value = fndrange.Offset(3, -2).Value
Cells(i + 7, 11).Value = fndrange.Offset(2, -2).Value
Cells(i + 8, 11).Value = fndrange.Offset(1, -2).Value
i = 1 + 1
Loop Until fndrange.Address = fstaddress
Else
MsgBox "XX"
End If
End Sub

lpk187

¦^´_ 5# missbb

§Ú¤£ª¾¹D¹ï¤£¹ï¡A¥H¤Uµ¹§A°Ñ¦Ò

1. Sub match2()
   
2. 'combine id card no and date for searching
   
3. Dim srcrange As Range, fndrange As Range
   
4. Dim fstaddress As String, i As Integer
   
5. Dim fc As Integer
   
6. Dim fr As Integer
   
7. Dim fr2 As Integer
   
8. Worksheets("attendance report").Activate
   
9. fc = 1
   
10. fr = 4
    
11. fr2 = 3
    
12. Set srcrange = Worksheets("data").Range("a4").CurrentRegion.Columns(9)
    
13. 
    
14. For Each Rng In [k3:ao3]
    
15.     Set fndrange = srcrange.Find(Cells(fr, fc) & Format(Rng, "d/m/yyyy"))
    
16.     If Not fndrange Is Nothing Then
    
17.         Cells(5, Rng.Column).Value = fndrange.Offset(, -2).Value
    
18.         Cells(6, Rng.Column).Value = fndrange.Offset(3, -2).Value
    
19.         Cells(12, Rng.Column).Value = fndrange.Offset(2, -2).Value
    
20.         Cells(13, Rng.Column).Value = fndrange.Offset(1, -2).Value
    
21.         i = 1 + 1
    
22.     Else
    
23.     MsgBox "XX"
    
24.     End If
    
25. Next
    
26. End Sub

½Æ»s¥N½X

lpk187

¦^´_ 7# missbb

(1) ¦p¦ó°µ¤U¤@­Ó­û¤u©O, ¦]¬°¦@¦³200¦h¤H?
Ans:
³o­n¬Ý§A­û¤u½s¸¹­n±q­þ¸ÌŪ¨ú¤F¡A¥u­nª¾¬ü¦b­þŪ¨ú­û¤u½s¸¹¡A´N¤ñ¸û¦n°µ¤F¡I


¦Ò¶Ôªí 20150815 v4.rar (113.63 KB)

¤U¸ü¦¸¼Æ: 7

2015-8-15 23:48

ÁÙ¦³¦³®ÉŪ¥Xªº¸ê®Æ¡A¦³5µ§³o®É¤S¸Ó¦p¦ó©O¡H
§Ú¤W¶ÇªºÀɮסA¬O§â²Ä5²Ä©ñ¦b¤U¯Z®É¶¡¡A²Ä4µ§«h¤£Åã¥Ü

lpk187

¦^´_ 9# missbb

¸Õ¸Õ¬Ý¡I
§ÚÁÙ¬O§Q¥Îee data¸ê®Æ§¨¨Ó°µ¡A¶â¡A³o¼Ë¤ñ¸û¤è«K¡I
    ¦Ò¶Ôªí 20150815 v4.rar (117.04 KB)

¤U¸ü¦¸¼Æ: 10

2015-8-16 00:48

lpk187

¦^´_ 11# missbb

§Úµo²{¡A¥i¯à§A·íªì¼g³oÀɮ׮ɴ¿²£¥Í·¸¦ì¦Ó¾É­PÀÉ®×Åܫܤj¡A¨Ó¨ì1.7M
¤w¸gÀ°§A³B²z¦n¤F(¤£¹Lµe­±¦³ÂI¤£¹³­ì¨Óªº¼Ë¤l)¡A¥t¥~·s¼W¥[¤@¨Ç¥\¯à¡¨²M°£¸ê®Æ¡§ ¤Î ¡¨¦Û°Ê¶ñ¼g¤é´Á¡§¥H¤Îµ{¦¡¸Ñ»¡¡A¥H§Q§A©úÁAµ{¦¡ªº°Ê§@
   
¦Ò¶Ôªí 20150815 v5.rar (44.95 KB)

¤U¸ü¦¸¼Æ: 20

2015-8-16 12:10

lpk187

¦^´_ 15# missbb

ActiveSheet.Range("A4:AP23").Select
   
    Selection.Copy¦¸<<³o¸Ì¸Ì¬O¿ï¾Ü¤W­±½d³òA4:AP23»s½d³òªº½Æ»s
¦ý¤U­±ªº¬õ¦â³¡¥÷«o§â¤W­±ªº¿ï¾Ü§ó´«±¼¤F¡A©Ò¥H¥¦¥u¯à¶K¤@¦¸¡A²Ä¤G¦¸´N·|¥X¿ù¤F¡I
For i = 1 To QQQQ
     ActiveSheet.Range("A" & 1 + i * 20).Select
    ActiveSheet.Paste
  Next i
­n½Æ»s¶K¤W¡A¥u­n¤@¥y´N¥i¸Ñ¨M¡A¦p¤U½d¨Ò¡G

1. Sub COPY_FORMAT()
   
2. Dim i As Integer
   
3.     Worksheets("attendance report").Activate
   
4.     QQQQ = Worksheets("ee data").Cells(Rows.Count, "b").End(xlUp).Row
   
5. For i = 1 To QQQQ
   
6.     ActiveSheet.Range("A4:AP23").Copy Range("A" & 1 + i * 20)
   
7. Next i
   
8. End Sub

½Æ»s¥N½X