[µo°Ý] ¦Ò¶ÔªíVBAÃøÃD¨D§U

missbb

¥»¤H¤@ª½¾Ç²ßVBA¤Îª¦¤å, µo²{³æ¤@VBA°ÝÃD¤£¤j, ¦ý­n­Ó¥\¯à´N¼Z¤J§x§½, ¤S¬O¦Ò¶Ôªí°ÝÃD, ´Á±æµ¹¤©¨ó§U:
¦Ò¶Ôªí 20150815.rar (109.15 KB)

¤U¸ü¦¸¼Æ: 5

2015-8-15 17:37

±q¾Ç²ß®Ñ¤º§Û¦Ò¥Î¤F¬Û«H¬O·MÄøªº¤èªk, ¨D±Ð::Q

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 = 12
fr = 4
fr2 = 3

Set srcrange = Worksheets("data").Range("a4").CurrentRegion.Columns(9)
Set fndrange = srcrange.Find(what:=Range(fr, fc) & Range("v" & fr2).Value)

If Not fndrange Is Nothing Then

fstaddress = fndrange.Address

i = 5

Do
Cells(i, 22).Value = fndrange.Offset(, 0).Value
Cells(i + 1, 22).Value = fndrange.Offset(1, 0).Value
Cells(i + 8, 22).Value = fndrange.Offset(2, 0).Value
Cells(i + 9, 22).Value = fndrange.Offset(3, 0).Value

i = 1 + 1

Loop Until findrange.Address = fstaddress

Else

MsgBox "XX"
End If



End Sub

missbb

¦^´_ 2# lpk187

§A¦n, §Ú§ï¤F¦ý¥u¥i¥X²{1­Ó¤H¤Î1¤éªº¸ê®Æ, ¨º­ÓLOOP¤£ª¾¦p¦ó³B²z::(

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) & Cells(fr2, 11).Value)

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

missbb

¦^´_ 4# lpk187

§A¦n, §Úªº¸Ñ»¡¤Î­n¨D, ¦³³Ò¬Ý¬Ý! «ô°U:L

¤U¸ü (44.82 KB)

2015-8-15 20:13

missbb

¦^´_ 6# lpk187

«Ü¦hÁÂ, ¬O³o¼Ëªº·Qªk, ¦ýÁÙ¦³2ÂI: (1) ¦p¦ó°µ¤U¤@­Ó­û¤u©O, ¦]¬°¦@¦³200¦h¤H? (2)¦pªG®É¶¡¥u¦³3­Ó, ¦p7¤ë1¤é, µ{¦¡¤¤´N¦h¨ú¤F7¤ë3¤éªº²Ä¤@­Ó®É¶¡, ­n§@¥X­­¨î¦p¹Ï©Ò¥Ü:·P¿E¤£ºÉ

¤U¸ü (143.75 KB)

2015-8-15 22:10

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

¤U¸ü¦¸¼Æ: 3

2015-8-15 22:11

missbb

¦^´_ 8# lpk187
®É¶¡§ä¨ú¥¿½T! ²Ä5µ§(©Î³Ì«á¤@µ§)µø¬°¤U¯Z´N¹ï¤F, ¨ä¥L·|¯dªÅ. «Ü¼F®`!
¦A¦¸·P¿E, ¦]¬°­û¤u¸ê®Æ»P¥´Íü®É¶¡©ñ¦b¤£¦PSHEETS, ½Ð¬Ý¹Ï¤º¸ê®Æ::D

¤U¸ü (45.36 KB)

2015-8-16 00:10

missbb

¦^´_ 10# lpk187


    ¤Ó·PÁ¤F, §Ú·|¾Ç²ß·í¤¤¹B¥Î, ¦³³Ò¤j¥q:'( ¬°§Ú¸Ñ¨M¤FÀYµh¦h¤ëªº°ÝÃD!

missbb

¦^´_ 12# lpk187

¬O§r, ¥Î¨ç¼Æ¥OFILE¹B§@«ÜºC¤~¥ÎVBA, ·PÁ²Ӥߪº¼W­È:'(

missbb

¦^´_ 14# GBKEE
·Q½Ð±Ð±N("A4:AP23") COPY µ¥¦p­û¤u¤H¼Æªº¦¸¼Æ, ¦ý¥X¿ù, ¬O¬Æ»ò­ì¦]?

1. [code]Sub COPY_FORMAT()
   
2. 
   
3. Dim i As Integer
   
4. 
   
5.     Worksheets("attendance report").Activate
   
6.    
   
7.     QQQQ = Worksheets("ee data").Cells(Rows.Count, "b").End(xlUp).Row
   
8.    
   
9.     ActiveSheet.Range("A4:AP23").Select
   
10.    
    
11.     Selection.Copy
    
12.    
    
13. For i = 1 To QQQQ
    
14.    
    
15.     ActiveSheet.Range("A" & 1 + i * 20).Select
    
16.     ActiveSheet.Paste
    
17.   
    
18. Next i
    
19. End Sub
    
20. 
    
21.         
    
22. 
    
23. 
    
24.    

½Æ»s¥N½X

[/code]

missbb

¦^´_ 16# lpk187
¦hÁ«ü¾É.

missbb

¦^´_ 14# GBKEE

·Q°Ý¬°¦ó¥i¥H¨ú±o©m¦W©O?¦]¬°§Ú¦bEE DATA¤ÎEE¤º³£§ä¤£¨ì¹ïÀ³ªºÄæ?

           .Parent.Cells(.Row + X + 1, "d").Resize(20) = Rng

¥t¥i§_±qEE DATA¤@ª©¤º±N­û¤u½s¸¹¤Î³¡ªù³£©ñ¦bATTENDANCE¤º¨ÃÅã¥Ü20¦C©O?

¦³³Ò½ç±Ð!