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

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

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

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

±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

¦^´_ 1# missbb

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-8-15 20:02 ½s¿è

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

TOP

¦^´_ 4# lpk187

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

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-8-15 21:15 ½s¿è

¦^´_ 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. For Each Rng In [k3:ao3]
  14.     Set fndrange = srcrange.Find(Cells(fr, fc) & Format(Rng, "d/m/yyyy"))
  15.     If Not fndrange Is Nothing Then
  16.         Cells(5, Rng.Column).Value = fndrange.Offset(, -2).Value
  17.         Cells(6, Rng.Column).Value = fndrange.Offset(3, -2).Value
  18.         Cells(12, Rng.Column).Value = fndrange.Offset(2, -2).Value
  19.         Cells(13, Rng.Column).Value = fndrange.Offset(1, -2).Value
  20.         i = 1 + 1
  21.     Else
  22.     MsgBox "XX"
  23.     End If
  24. Next
  25. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ missbb ©ó 2015-8-15 22:13 ½s¿è

¦^´_ 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¤£ºÉ
¦Ò¶Ôªí 20150815 v4.rar (284.58 KB)

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-8-15 23:55 ½s¿è

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

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

TOP

¥»©«³Ì«á¥Ñ missbb ©ó 2015-8-16 00:13 ½s¿è

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

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-8-16 00:50 ½s¿è

¦^´_ 9# missbb

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

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD