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

[­ì³Ð] ¥HÃöÁä¦r¿z¿ï¦h­Ósheetsªº¸ê®Æ

[­ì³Ð] ¥HÃöÁä¦r¿z¿ï¦h­Ósheetsªº¸ê®Æ

³o¬O¨Ï¥ÎVBAªº¤èªk, ¹L´X¤Ñ¦A°µ­Ó¥Î¨ç¼Æ(¤£¥ÎVBA)ªº¤èªk

sonny1.gif
2013-5-10 01:31

search record by keyword - Sonny_VER1.rar (21.89 KB)
ss

¦^´_ 1# sunnyso

¼gªº¤£¿ù¡Aºc·Q¤]¤£¿ù

¦pªG¤@¶}©l¨S¦³¿z¿ïÃöÁä¦r®É¡A¦p¦ó§ï¬°¥þ³¡Åã¥Ü

¥Ø«e¤@¦CÃöÁä¦rªºÃö«Y¦¡¬O AND¡A¬O§_¯à¦h´X¦C¬° OR
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  sunnyso

¼gªº¤£¿ù¡Aºc·Q¤]¤£¿ù

[1] ¦pªG¤@¶}©l¨S¦³¿z¿ïÃöÁä¦r®É¡A¦p¦ó§ï¬°¥þ³¡Åã¥Ü

¥Ø«e¤@¦CÃö ...
ML089 µoªí©ó 2013-5-11 09:19


[1] ¦b For Each sh In all_datasheetsªºNext «e¥[¤J, "Cells(dst_row, 1).Resize(src_rows, clmn1) = tmp_data" §Y¥i

[2] ­Y­n¥ÎOR ´N­n§ï°Ê¦h¤@¨Ç¥N½X, ¹L¨â¤Ñ§ï¤@¤U©ñ¤W¨Ó.
  1.     For Each sh In all_datasheets
  2. ......
  3. ......
  4.         Cells(dst_row, 1).Resize(src_rows, clmn1) = tmp_data
  5.     Next
½Æ»s¥N½X
ss

TOP

¦^´_ 3# sunnyso


    §ï¦¨³o¼Ë¦n¥Î¦h¤F

ªíÀY§Ú³]¦n¦r«¬»PÃC¦â¡A¥u­n¤@°õ¦æVBA«á´N·|³Q²M°£? ¦³¿ìªk¤£³Q²M°£¶Ü?
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  sunnyso


    §ï¦¨³o¼Ë¦n¥Î¦h¤F

ªíÀY§Ú³]¦n¦r«¬»PÃC¦â¡A¥u­n¤@°õ¦æVBA«á´N·|³Q²M°£? ¦³¿ìªk¤£ ...
ML089 µoªí©ó 2013-5-11 16:19


1. ¦bAAÄæ³]­pªíÀYªº¦r«¬»PÃC¦âµ¥ (VBA Sheet)
2. ²K¥[VBA OR¿z¿ï¡A¦bÃöÁä¦r«e¥[#
3. ²K¥[«DVBA¤èªk¡A·í¤¤¨S¦³¨Ï¥Î°}¦C©M°}¦C¨ç¼Æ¡]¦psmall, minµ¥¡^¡A¥H¤ÎºÉ¶qÁקK¨Ï¥ÎIF¨ç¼Æ¡]¦]¦¹¿z¿ï«áªº¸ê®Æ¶¶§Ç¬O3­Ósheets¥æ¿ù¡^¡C¥H§K¸ê®Æ¼W¥[·|¥O­pºâ¹Lªø¡C

½Ð«ü±Ð¡C

Sonny.JPG
2013-5-12 02:39

search record by keyword - Sonny.rar (37.13 KB)
ss

TOP

¦^´_ 5# sunnyso

>1. ¦bAAÄæ³]­pªíÀYªº¦r«¬»PÃC¦âµ¥ (VBA Sheet)
¨S¦³¸Õ¦¨¥\

>2. ²K¥[VBA OR¿z¿ï¡A¦bÃöÁä¦r«e¥[#
³o³¡¤À¥Îªº¦³ÂI¤£¶¶
¦p¯à¹³°ª¶¥¿z¿ï¤è¦¡¤ñ¸û²³æ¡A¦P¤@¦C¬°AND¡A¤£¦P¦C¬°OR
VBA¥i¥H¦A¦P¤@¦C¥H ";" °Ï¤À¬°¤£¦P¦C¤è¦¡¡A
¨Ò¦p
¤p¶µ=¦­;¤È¡AS=A ªí¥Ü¨ú¥X OR(AND(¤p¶µ=¦­,S=A), AND(¤p¶µ=¤È,S=A))
¤p¶µ=¦­;¤È¡AS=A;B ªí¥Ü¨ú¥X OR(AND(¤p¶µ=¦­,S=A), AND(¤p¶µ=¤È,S=B))

>3. ²K¥[«DVBA¤èªk¡A·í¤¤¨S¦³¨Ï¥Î°}¦C©M°}¦C¨ç¼Æ¡]¦psmall, minµ¥¡^¡A¥H¤ÎºÉ¶qÁקK¨Ï¥ÎIF¨ç¼Æ¡]¦]¦¹¿z¿ï«áªº¸ê®Æ¶¶§Ç¬O3­Ósheets¥æ¿ù¡^¡C¥H§K¸ê®Æ¼W¥[·|¥O­pºâ¹Lªø¡C
¤½¦¡¤j·§¥u¯à³o¼Ë°µ¡A¥þ³¡¥ý¤Þ¥Î¹L¨Ó¿z¿ï   


¥H«e¥Î¥¨¶°½m²ß¹L¦¹Ãþ°ÝÃD¡A¬Ý§A¨Ï¥ÎVBA¨Ó³B²z©Ò¥H¯S§O¦³¿³½ì¡C¦pªG§A²Ö¤F¤£¥ÎºÞ§Úªº»Ý¨D¡CÁÂÁÂ
¥H«e¬O¤À¸Ñ°Ê§@
1. ±N¦USheets¸ê®Æ½Æ»s¦A¤@°_
2. ¤W¤è¹w¯d¤­¦æ¥Î°ª¯Å¿z¿ï¤è³B²z¡A¸ê®Æ¶ñ§¹«á«ö¶s¥Ñ¥¨¶°¨Ó±Ò°Ê°ª¯Å¿z¿ï(¥b¦Û°Ê¤è¦¡)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  sunnyso

¼gªº¤£¿ù¡Aºc·Q¤]¤£¿ù

¦pªG¤@¶}©l¨S¦³¿z¿ïÃöÁä¦r®É¡A¦p¦ó§ï¬°¥þ³¡Åã¥Ü

¥Ø«e¤@¦CÃö ...
ML089 µoªí©ó 2013-5-11 09:19

¦^´_ 3# sunnyso
Private Sub Worksheet_Activate()
    ' ....
    ' ....
    Cells.ClearContents
    ' ....
    ' ....
    ' ....
    Next
    '  ³]©wµL¿z¿ïÃöÁä¦r¿é¤J¡A¨C¦¸±Ò°Ê¸Ó­¶­±§YÅã¥Ü¥þ³¡¤º®e
    '  ¥H¤UNull("")«Å§i¡A¨t²Î·|¦Û°ÊIJµoWorksheet_Change()
    Cells(4, 1) = ""   
    Application.ScreenUpdating = True
End Sub

TOP

¦^´_ 6# ML089

1. ³]¸mªíÀY®æ¦¡ (¨£¤U¹Ï)

2. «Ü¦nªº«Øij, ¦³®É¶¡¦A§@­×§ï

3. ¥Ñ©ó¬YºØ¯S®í±¡ªp¤U, ¤£¯à¨Ï¥Î¦³¥¨¶°ªºÀÉ®×, ©Ò¥H¤~³]­p¸Ó¤è®×, ©ñ¤W¨Ó¸ò¤j®a¤À¨É.

sonny2.gif
2013-5-12 19:54
ss

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-5-12 21:36 ½s¿è

¦^´_ 5# sunnyso
§Aªºµ{¦¡§ï¥Î¦Û°Ê¿z¿ï ¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Private Sub Worksheet_Activate()
  3.     Dim Sh As Worksheet, E As Integer
  4.     Application.ScreenUpdating = False
  5.     Application.EnableEvents = False
  6.     AutoFilterMode = False
  7.     E = Cells(Rows.Count, "A").End(xlUp).Row
  8.     E = IIF(E = 5, 6, E)
  9.     Range("A6:E" & E).Clear
  10.     For Each Sh In Sheets(Array("DataSheet2", "DataSheet3", "DataSheet3"))
  11.         Sh.UsedRange.Offset(1).Copy Cells(Cells(Rows.Count, "A").End(xlUp).Row + 1, "A")            '¦X¨Ö¸ê®Æ
  12.     Next
  13.     E = Cells(Rows.Count, "A").End(xlUp).Row
  14.     Range("A5:E" & E).AutoFilter                '½d³ò³]¥ß,¦Û°Ê¿z¿ï.
  15.     For E = 1 To 5
  16.         Range("A5").AutoFilter E, , , , False   '¦Û°Ê¿z¿ï: ¨ú®ø½bÀY
  17.     Next
  18.     Application.EnableEvents = True
  19.     Application.ScreenUpdating = True
  20. End Sub
  21. Private Sub Worksheet_Change(ByVal Target As Range)
  22.     Dim M As Variant
  23.     Application.EnableEvents = False
  24.     If Target.Row = 4 And Target.Column <= 5 Then
  25.         M = Split(Target, "#")
  26.         If UBound(M) >= 1 Then   '
  27.            '¦Û°Ê¿z¿ï: ¦bÃöÁä¦rµü«e¥[¤J¡i#¡j±N¥HOR¨Ó¿z¿ï¸ÓÄæ¡C
  28.             Range("A5").AutoFilter Target.Column, "=*" & M(0) & "*", xlOr, "=*" & M(1) & "*"
  29.         Else
  30.             Range("A5").AutoFilter Target.Column, "=*" & Target & "*"
  31.         End If
  32.     End If
  33.     Application.EnableEvents = True
  34. End Sub
  35. Private Sub Worksheet_SelectionChange(ByVal Target As Range)  '²Ä4¦C¿é¤J«áªð¦^²Ä4¦C
  36.     Application.EnableEvents = False
  37.     If Target.Row = 5 And Target.Column <= 5 Then
  38.         Selection.Offset(-1).Select         'Target.Offset(-1).Select
  39.     End If
  40.     Application.EnableEvents = True
  41. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  sunnyso
Private Sub Worksheet_Activate()
    ' ....
    ' ....
    Cells.ClearContent ...
c_c_lai µoªí©ó 2013-5-12 19:54


Cells.ClearContent¨Ã¤£·|IJ°Ê worksheet_change
ss

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD