[µ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

lpk187

¦^´_ 1# missbb

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

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

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

missbb

¦^´_ 4# lpk187

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

¤U¸ü (44.82 KB)

2015-8-15 20:13

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

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

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¤£Åã¥Ü

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

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