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

[µo°Ý] Äæ¦ì¶K¤W°ÝÃD

¤é´Áªº·j´M¤èªk

·Q½Ð°Ý¤é´Áªº·j´M¤èªk
¬O¤£¬O¸ò¤@¯ë·j´M¤£¤Ó¤@¼Ë?

Book1.rar (43.62 KB)

°ÝÃD:
«ö¤UŪ¥X ¿é¤J¤é´Á ªº¤é´Á®æ¦¡À³¸Ó¬O­n«ç»ò¼Ë
ex¥´¤J2014/7/1 ·|±Nsheet2ªº7/1ªº©Ò¦³¸ê®Æ³£§ó·s¦ÜSheet1²Ä¤T¦C¶}©l

©Ò¥H·Q°Ý¤é´Áªº·j´M¤èªk¬O«ç»ò°µ

TOP

¦^´_ 1# bridetobe
  1. Private Sub CommandButton1_Click()
  2.     Dim a_date  ' As String
  3.     a_date = InputBox("¿é¤J¤é´Á(¨Ò2014/7/1):")
  4.     With Sheet2
  5.         Set A = .Range("A2", .[a2].End(xlDown)).Find(what:=a_date, LookIn:=xlValues) '·j´M¤å¦r
  6.     End With
  7.     If Not A Is Nothing Then
  8.         MsgBox ("¦³")
  9.     Else
  10.         MsgBox ("¨S¦³¸ê®Æ")
  11.     End If
  12. End Sub
  13. Private Sub CommandButton2_Click()
  14.     Dim a_date As Date
  15.     a_date = InputBox("¿é¤J¤é´Á(¨Ò2014/7/1):")
  16.     With Sheet2
  17.         Set A = .Range("A2", .[a2].End(xlDown)).Find(what:=a_date, LookIn:=xlFormulas) '·j´M¤½¦¡
  18.     End With
  19.     If Not A Is Nothing Then
  20.         MsgBox ("¦³")
  21.     Else
  22.         MsgBox ("¨S¦³¸ê®Æ")
  23.     End If
  24. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE

ÁÂÁª©¥D¦^µª~~
¨º§Ú¥i¥H¦b½Ð°Ý¤@¤U
Set A = .Range("A2", .[a2].End(xlDown)).Find(what:=a_date, LookIn:=xlValues) '·j´M¤å¦r

³o­Ó½d³ò³¡¤Àªº "A2", .[a2].End(xlDown))¥Îªk¶Ü

¥H¤Îbutton1©M2ªº®t§O

´ú¸Õ.rar (43.63 KB)
§Ú±N·j´M¨ìªº¸ê®Æ ¶K¨ìsheet1ªº²Ä¤T¦C¶}©l
¬O­þ¸Ìªº¼gªk¿ù»~
Åý¥LµL¤îºÉªº¶K¤U¥h
¦Ó¥Bsheet2ªº«~¦W#702 ¤]´N¬O²Ä¤@¶µ¨S¦³¶K¨ì??

¤£¦n·N«ä°ÝÃD«Ü¦h><

TOP

¦^´_ 2# GBKEE


    ´ú¸Õ.rar (47.71 KB)

©êºp §¨±a¿ùÀÉ®× ³o­Ó¤~¹ï

TOP

¦^´_ 4# bridetobe
¸Õ¸Õ¬Ý
  1. Private Sub CommandButton1_Click()
  2.     Dim srcrange As Range
  3.     Dim a_date  ' As String
  4.     Dim fndrange, fstaddress, i
  5.     a_date = InputBox("¿é¤J¤é´Á(¨Ò2014/7/1):", , "2014/7/1")
  6.     If a_date = "" Then Exit Sub  '¨ú®ø®É
  7.     With Sheet2
  8.     Set srcrange = .Range("A2", .[a2].End(xlDown))
  9.         Set fndrange = srcrange.Find(what:=a_date, AFTER:=srcrange(srcrange.Count), LookIn:=xlValues) '·j´M¤å¦r
  10.         'After     ¿ï¾Ü©Êªº Variant¡C  «ü©wÀx¦s®æ¡A´M§ä±N±q¸ÓÀx¦s®æ¤§«á¶}©l¡C¦¹Àx¦s®æ¹ïÀ³©ó±q¨Ï¥ÎªÌ¤¶­±´M§ä®Éªº¨Ï¥Î¤¤Àx¦s®æ¦ì¸m¡C
  11.         'ª`·N After ¥²¶·¬O´M§ä½d³ò¤¤ªº³æ­ÓÀx¦s®æ¡C½Ð°O¦í´M§ä¬O±q¸ÓÀx¦s®æ¤§«á   ¶}©lªº¡F¥²¶·µ¥¨ì¸Ó¤èªk´`Àô¦^¨ì¦¹Àx¦s®æ®É¡A¤~·|·j´M¨ä¤º®e¡C
  12.         '¦pªG¥¼«ü©w¦¹¤Þ¼Æ¡A·j´M±N±q½d³òªº¥ª¤W¨¤Àx¦s®æ¤§«á¶}©l
  13.     End With
  14.     If Not fndrange Is Nothing Then
  15.         'fstaddress = fndress = fndrange.Address  '³o¸Ì¿ù»~ Åý¥LµL¤îºÉªº¶K¤U¥h
  16.         fstaddress = fndrange.Address
  17.         i = 3
  18.         Do
  19.         Cells(i, 1).Value = fndrange.Offset(, 1)
  20.         Cells(i, 2).Value = fndrange.Offset(, 2)
  21.         Cells(i, 3).Value = fndrange.Offset(, 3)
  22.         Cells(i, 4).Value = fndrange.Offset(, 4)
  23.         Cells(i, 5).Value = fndrange.Offset(, 5)
  24.         Cells(i, 6).Value = fndrange.Offset(, 6)
  25.         Cells(i, 7).Value = fndrange.Offset(, 7)
  26.         Set fndrange = srcrange.FindNext(AFTER:=fndrange)
  27.         i = i + 1
  28.         Loop Until fndrange.Address = fstaddress
  29.     Else
  30.         MsgBox ("¨S¦³¸ê®Æ")
  31.     End If
  32. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 5# GBKEE

ÁÂÁÂ
¸Ñ¨M¤FµL¤îºÉ¶K¤U¥h¤F
¥i¬Oµo²{¤£¥u¶K¤W7/1¸¹ªº
ÁÙ¦³§¨Âø¤@¨Ç¨ä¥Lªº¤é´Áªº
7/7 µ¥µ¥ªº ¶K¨ì¤F7xx¦h¦C

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-7-30 15:15 ½s¿è

¦^´_ 6# bridetobe
Find ·|®Ú¾Ú¤W£¸¦¸Findªº°Ñ¼Æ¨Ó·j´M
  1. Private Sub CommandButton1_Click()
  2.     Dim srcrange As Range
  3.     Dim a_date  As String
  4.     Dim fndrange As Range, fstaddress As String, i As Integer
  5.     a_date = InputBox("¿é¤J¤é´Á(¨Ò2014/7/1):", , "2014/7/1")
  6.     If a_date = "" Then Exit Sub  '¨ú®ø®É
  7.     With Sheet2
  8.     Set srcrange = .Range("A2", .[a2].End(xlDown))
  9.         srcrange.Interior.ColorIndex = xlNone
  10.         Set fndrange = srcrange.Find(what:=a_date, AFTER:=srcrange(srcrange.Count), LookIn:=xlValues, lookat:=xlWhole) '·j´M¤å¦r
  11.         'After     ¿ï¾Ü©Êªº Variant¡C  «ü©wÀx¦s®æ¡A´M§ä±N±q¸ÓÀx¦s®æ¤§«á¶}©l¡C¦¹Àx¦s®æ¹ïÀ³©ó±q¨Ï¥ÎªÌ¤¶­±´M§ä®Éªº¨Ï¥Î¤¤Àx¦s®æ¦ì¸m¡C
  12.         'ª`·N After ¥²¶·¬O´M§ä½d³ò¤¤ªº³æ­ÓÀx¦s®æ¡C½Ð°O¦í´M§ä¬O±q¸ÓÀx¦s®æ¤§«á   ¶}©lªº¡F¥²¶·µ¥¨ì¸Ó¤èªk´`Àô¦^¨ì¦¹Àx¦s®æ®É¡A¤~·|·j´M¨ä¤º®e¡C
  13.         '¦pªG¥¼«ü©w¦¹¤Þ¼Æ¡A·j´M±N±q½d³òªº¥ª¤W¨¤Àx¦s®æ¤§«á¶}©l
  14.                
  15.         'LookAt  ¿ï¾Ü©Êªº Variant¡C¥i¬°¤U¦C XlLookAt ±`¼Æ¤§¤@¡GxlWhole ©Î xlPart¡C
  16.         
  17.     End With
  18.     If Not fndrange Is Nothing Then
  19.         'fstaddress = fndress = fndrange.Address  '³o¸Ì¿ù»~ Åý¥LµL¤îºÉªº¶K¤U¥h
  20.         fstaddress = fndrange.Address
  21.         i = 3
  22.         Do
  23.             fndrange.Interior.Color = vbRed
  24.             Cells(i, 1).Resize(1, 7) = fndrange.Offset(, 1).Resize(1, 7).Value
  25.             Set fndrange = srcrange.FindNext(AFTER:=fndrange)
  26.             i = i + 1
  27.         Loop Until fndrange.Address = fstaddress
  28.     Else
  29.         MsgBox ("¨S¦³¸ê®Æ")
  30.     End If
  31. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE


¸Ñ¨M¤F
«D±`ÁÂÁª©¤j~~~

TOP

[µo°Ý] Äæ¦ì¶K¤W°ÝÃD

¶î®Æ¦s³fºÞ²z(20140806).rar (63.7 KB)

·Q½Ð°Ý¤@¤U
«ö¤UŪ¥X¥i¥H±N8/6ªº¸ê®Æ¥þ³¡Åª¨ú¥X¨Ó
µM«á§Q¥Î"¾Þ§@"¤u§@ªí §ó§ï8/7ªº¦s³f

«ö¤U¼g¤J¥i¥H
¸õ¥X¤@­Óinputbox¸ß°Ý ­n¼W¥[¬°´X¤ë´X¸¹ªº¸ê®Æ
¦A±N§ó§ï§¹ªº¸ê®Æ(°£¤FA1:G2)ªº¸ê®Æ

(¹Ï¤¤¬õ®Ø®Ø³¡¤À)
©¹"¦s³f¸ê®Æ"¤u§@ªíBÄæ³Ì¤U­±©¹¤U¶K

(¹Ï¤¤¬õ½u³¡¤À)
µM«á±NInputbox¤ºªº¤é´Á¶K¨ì»P¸ê®Æ¦P¦æ

(±N¹Ï¤¤AÄæªÅ¥Õ¸ê®Æ¥Îinputbox¤ºªº¤º®e¶ñº¡)
½Ð°Ý³o¨Ç¨BÆJ³o­n«ç»ò§¹¦¨?

­ì¥»¦³·Q¹L ¨Ï¥Î¥¨¶°¿ý»s
±N¾ã­Ó¾Þ§@¤u§@ªíªº¤º®e
½Æ»s¨ì¨ä¥L¤u§@ªí µM«á§â¼ÐÃD¦C(A1:G2)§R°£
¦A½Æ»s¨ì¦s³f¸ê®Æ¤u§@ªí¤º
¦ý«ç»ò¼g³£¼g¤£¥X¨Ó
ÁٽЦU¦ì¤j¤jÀ°À°¦£

¥»©«³Ì«á¥Ñ GBKEE ©ó 2014-8-7 10:47 ½s¿è

¦^´_ 9# bridetobe
  1. Private Sub CommandButton1_Click()
  2.     Dim srcrange As Range
  3.     Dim a_date  As String
  4.     Dim fndrange As Range, fstaddress As String, i As Integer
  5.     a_date = InputBox("¿é¤J¤é´Á(¨Ò2014/7/1):", , "2014/7/1")
  6.     Application.ScreenUpdating = False
  7.    '******************
  8.     Range("A3", Range("A3").End(xlDown)).Resize(, 7).ClearContents
  9.     '*****************
  10.     If a_date = "" Then Exit Sub
½Æ»s¥N½X
  1. Private Sub CommandButton2_Click()
  2.    Dim Rng As Range
  3.    With Sheets("¾Þ§@")
  4.        Set Rng = .Range("A3", .Range("A3").End(xlDown)).Resize(, 7)
  5.        With Sheets("¦s³f¸ê®Æ").Range("B1").End(xlDown).Offset(1)
  6.             .Resize(Rng.Rows.Count, Rng.Columns.Count) = Rng.Value
  7.             .Offset(, -1).Resize(Rng.Rows.Count) = Sheets("¾Þ§@").[b1]
  8.         End With
  9.     End With
  10. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD