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

[µo°Ý] VBA ·j´M¸ê®Æ¦C¹ïÀ³ªº³Ì¤j­È©Î³Ì¤p­È

[µo°Ý] VBA ·j´M¸ê®Æ¦C¹ïÀ³ªº³Ì¤j­È©Î³Ì¤p­È

¦U¦ì«e½ú¤j®a¦n:
¤p§Ì­è¶}©l¦Û¾ÇEXCEL VBA µ{¦¡¤@¬q®É¶¡
¨C¦¸±qºô¸ô¤W·j´M°ÝÃD¸Ñµª³£·|·j´M¨ì³o­Ó½×¾Â
ª¾¹D³o­Ó½×¾Âªº«e½ú³£¨­Ãhµ´§Þ....
­è¦n³Ìªñ¦³­Ó°ÝÃD
±qºô¸ô¤W¤Î¦Û¤v¶Rªº®Ñ¤W³£·j´M¤£¨ì¦nªº¸Ñªk
¦]¦¹´Nµù¥U·|­û¨Ó¦V¦U¦ì«e½ú½Ð±Ð
§Æ±æ©¹«á¤]¯à¦b³o­Óª©¤W©M¤j®a¤@°_¾Ç²ß^^



°ÝÃD¦p¤U:
¦p·Ó¤ù¤¤ªº¨Ò¤l, ¥ªªí¬°±qACEESS¶×¤Jªº¸ê®Æ
¤p§Ì§Æ±æ¯à±N¸ê®Æ¾ã²z¾ã²z¦¨¥ªªíªº§Î¦¡
´N¬O±N"¯Z¯Å"¨ºÄæ©Ò¹ïÀ³¨ì©Ò¦³ªº"¦~¬ö"§@³Ì¤j­È¤Î³Ì¤p­Èªºªº·j´M
¤]´N¬O¹³ACEESS ¤¤¡y¬d¸ßºëÆF¡zªº¥\¯à

¥H¤U¨âºØ¤è¦¡¬O¤p§Ì·Q¨ìªº, ³£¥i¥H¸Ñ¨M§Úªº°ÝÃD:
1. ±qACCESS¶×¤J¸ê®Æ®É´Nª½±µ±N­ì©l¸ê®Æ¥Î¡y¬d¸ßºëÆF¡z¥ý°µ¾ã²z«á¦A¶×¤JEXCEL¤u§@ªí  (¤£ª¾¹DVBA¦³¨S¦³³oºØ¥\¯à¥i¥H©I¥s¬d¸ßºëÆF ?)

2. ±N­ì©l¸ê®Æ¦p·Ó¤ù¥ý¶×¤JEXCEL¤u§@ªí«á¦A°µ¾ã²z


¦A³Â·Ð¦U¦ì«e½ú«ü±Ð¤F!  ·P¿E¤£ºÉ~~!

¥»©«³Ì«á¥Ñ owen06 ©ó 2014-11-28 10:47 ½s¿è

¦^´_ 1# junkwei


    §ä´M²Å¦X±ø¥óªº³Ì¤j­È¡G
    ¦bF2¿é¤J=MAX(($A$2:$A$16=E2)*$C$2:$C$16)¡A¦¹¬°°}¦C¤½¦¡¡A¿é¤J§¹¦¨«á­n«öCTRL+SHIFT+ENTER¡A
    ¦A§â¤½¦¡©¹¤U©Ô§Y¥i¡C
   
    §ä´M²Å¦X±ø¥óªº³Ì¤p­È¡G
    ¦bG2¿é¤J=MIN(IF($A$2:$A$16=E2,$C$2:$C$16,FALSE))¡A¦¹¬°°}¦C¤½¦¡¡A¿é¤J§¹¦¨«á­n«öCTRL+SHIFT+ENTER¡A
    ¦A§â¤½¦¡©¹¤U©Ô§Y¥i¡C

TOP

¦^´_ 1# junkwei

sql»yªk§Y¥i§¹¦¨
stock.mdb=>´«¦¨§Aªº¸ê®Æ®w¦WºÙ
¬Y¬Yªí=>´«¦¨§A¸ê®Æ®w¤¤ªº¸ê®Æªí¦WºÙ

¥H¤W¬Omdb»yªk¡A·s¸ê®Æ®w½Ð¦Û¦æ·j´M´À´«
  1. Sub ex()

  2.     Worksheets("1").Activate
  3.     Set cn = CreateObject("adodb.connection")
  4.     cn.Open ("Driver={Microsoft Access Driver (*.mdb)};dbq=" & ThisWorkbook.Path & "\stock.mdb")
  5.     Set rs = cn.Execute("select ¯Z¯Å,max(¦~¬ö) as ³Ì¤j¦~¬ö,min(¦~¯Å) as ³Ì¤p¦~¬ö from   ¬Y¬Yªí  group by ¯Z¯Å")
  6.     w = 1
  7.     For Each tt In rs.Fields 'Åã¥Üªí³æ¦WºÙ
  8.         Cells(w) = tt.Name
  9.         w = w + 1
  10.     Next
  11.     Cells(2, 1).CopyFromRecordset (rs)'¥s¥X¸ê®Æ
  12.     cn.Close
  13.     Set cn = Nothing
  14.     Set rs = Nothing

  15. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# junkwei
Excel ±q¿ï³æªº ¸ê®Æ>¨ú±o¥~³¡¸ê®Æ(±qAccess)>¿ïÀÉ®×>¶×¤J¸ê®Æµøµ¡: À˵ø¤è¦¡¤Ä¿ï¼Ï¯Ã¤ÀªRªí¡AµM«á©Ô¤@¤UÄæ¦ì´N¥i¥H¤F¡C
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

ÁÂÁ owen06  ¤Î stillfish00«e½úªº«ü¾É¡A¤£¹L¤p§Ì©Ò§Æ±æªº¤è¦¡¬O¥i¥H¥ÑVBAµ{¦¡¦Û°Ê¶×¤Jªº, ¦ýÁÙ¬OÁÂÁ§A­Ì¼·ªÅ«ü¾É, Åý§Ú¾Ç¨ìEXCEL¤£¦Pªº¤ÀªR¤èªk ~ ^^

TOP

¦^´_ 3# joey0415

ÁÂÁ«e½úªº«ü¾É~
¥Ñ©ó¤p§ÌVBA¸ê¾ú©|²L
¬O§_¥i¥H½Ð«e½ú¼·ªÅµy¥[»¡©ú¥Îªk¤Î»yªk©O
¥H«K¤p§Ì¤è«KÀ³¥Î¤Î¬d¸ß¬ÛÃöªº¸ê°T©O??

¨ä¤¤¦³´X¦æ¤p§Ì¤£¤Ó¤F¸Ñªº¦a¤è¦p¤U:
1.
Set rs = cn.Execute("select ¯Z¯Å,max(¦~¬ö) as ³Ì¤j¦~¬ö,min(¦~¯Å) as ³Ì¤p¦~¬ö from   ¬Y¬Yªí  group by ¯Z¯Å")   
¨ä¤¤ªº¡y ³Ì¤j¦~¬ö¡z¤Î¡y³Ì¤p¦~¬ö¡z¬O­n¦Û¤v¥ý¿é¤Jªº¶Ü?
P.S.¤p§Ìªº»Ý¨D¬O§Æ±æG2:I4ªºÀx¦s®æ³£¬OVBA¦Û°Ê¤ÀªR²£¥Íªº

2.
For Each tt In rs.Fields 'Åã¥Üªí³æ¦WºÙ
½Ð°Ýtt©Ò¥Nªíªº¬O¤u§@ªíªº¦WºÙ¶Ü?

¤£¦n·N«ä~~~
¦A³Â·Ð«e½ú¼·ªÅ«ü¾É¤F
·P¿E¤£ºÉ~~!!

TOP

¦^´_ 5# junkwei
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range, i As Integer, Ar()
  4.     Set Rng = Range("iv1")
  5.     '¶i¶¥¿z¿ï¥X"¯Z¯Å"Ä椣­«½Æªº­È
  6.     Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
  7.     ReDim Ar(1 To 3, 1 To 1) '°}¦C­«¸m
  8.     Ar(1, 1) = "¯Z¯Å"
  9.     Ar(2, 1) = "³Ì¤j¦~¬ö"
  10.     Ar(3, 1) = "³Ì¤p¦~¬ö"
  11.     i = 2
  12.     Do While Rng.Cells(i) <> "" '"¯Z¯Å"Ä椣­«½Æªº­È(¦U¯Z¯Å)
  13.        With Range("a:a")
  14.             .Replace Rng.Cells(i), "=EX", xlWhole   '¦U¯Z¯Å´«¸m¬°¿ù»~­È
  15.             With .SpecialCells(xlCellTypeFormulas, xlErrors)
  16.                 ReDim Preserve Ar(1 To 3, 1 To i)
  17.                 Ar(1, i) = Rng.Cells(i)
  18.                 Ar(2, i) = Application.Max(.Cells.Offset(, 2))
  19.                 Ar(3, i) = Application.Min(.Cells.Offset(, 2))
  20.                 .Value = Rng.Cells(i)              '¿ù»~­È´«¸m¦^¬°¦U¯Z¯Å
  21.             End With
  22.             i = i + 1
  23.        End With
  24.     Loop
  25.     Rng.EntireColumn.Clear              '²M°£:¶i¶¥¿z¿ï¥X"¯Z¯Å"Ä椣­«½Æªº­È
  26.     Range("g1").Resize(UBound(Ar, 2), UBound(Ar, 1)) = Application.WorksheetFunction.Transpose(Ar)
  27.     'UBound(Ar, 2)°}¦C²Ä2ºûªº¤¸¯À¤W­­­È
  28.     'UBound(Ar, 1)°}¦C²Ä1ºûªº¤¸¯À¤W­­­È
  29. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  junkwei
GBKEE µoªí©ó 2014-12-4 07:01


¯uªº«Ü·PÁÂGBKEE«e½úªº«ü¾É!!
³o­Ócode¥¿¬O§Ú­nªº¥\¯à!
vbaªGµM¥\¯à±j¤j...¤]¬Ý¨ì¦n¦h¨S¬Ý¹Lªº»yªk...0rz
¦ý¤p§Ì¬ã¨s«e½úªºcodeÁÙ¬O¦³¨Ç¦a¤èµLªk¤F¸Ñ...¤Wºô¬d¸ß¦ü¥G¤]ÁÙ¬O¤£¤ÓÀ´...
§Æ±æ«e½ú¥i¥H¤£§[«ü¾É..
1. Set Rng = Range("iv1")           
½Ð°Ý³o¼Ë©w¸qªºrange¬O«ç»ò¼Ëªºrange©O?? iv1 ¤£¬Oªí¥Üª©excelªº²Ä¤@Ä檺³Ì«á¤@®æ¶Ü?

2.  Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True     
½Ð°Ýa:a¬O«ç»ò¼Ëªº½d³ò©O??³o¦æcodeªºÅÞ¿è§Ú·Q¥b¤ÑÁÙ¬O·Q¤£³q...

3.½Ð°Ý¬°¤°»òcode¤¤­n§âAr()¯x°}¥ý¥HÂà¸m«áªº¦ì¸m¿é¤J¿z¿ï¡A¦A¥HÂà¸m«áªº¦ì¸m¿é¥X©O???

¤£¦n·N«ä¦pªG¦³±Ô­z¤£¦nªº¦a¤è½Ð¦h¨£½Ì
¦A³Â·Ð«e½ú«ü¾É¤F~ÁÂÁÂ~!!

TOP

¦^´_ 8# junkwei

Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
"a:a" -> AÄæ¾ãÄæ
AdvancedFilter ¤èªk expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
CriteriaRange ¨S¦³«ü©w½d³ò,¬°¤£³]¤U¿z¿ï±ø¥ó,
Ar([¬Û¹ï¬°¤u§@ªíªºÄæ¼Æ], [¬Û¹ï¬°¤u§@ªíªº¦C¼Æ]) ,»P¤u§@ªíªºCells(¦C,Äæ)ÄA­Ë
ReDim Preserve Ar(1 To 3, 1 To i)
¿ï¾Ü©Ê¤Þ¼Æ¡C·í§ïÅܭ즳°}¦C³Ì«á¤@ºûªº¤j¤p®É¡A¤´µM«O¦³­ì¨Óªº¸ê®ÆªºÃöÁä¦r¡C
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  junkwei

Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
"a:a" -> AÄæ¾ãÄæ
AdvancedF ...
GBKEE µoªí©ó 2014-12-12 07:13


­ì¨Ó¦p¦¹~~¯uªºÁÂÁÂGBKEEª©¥Dªº«ü¾É!!

Ãö©ó±zªºcode¤p§Ì©|¦³´X­Ó°ÝÃD·Q¦V±z¦A½T»{¤@¤U:
1. ½Ð°Ý¤@¶}©lªº Set Rng = Range("iv1") ¬O¤£¬OÀH«K«ü©w¤@­Órange´N¥i¥H¤F©O??
¦]¬°¤£¬O«á­±´N³QAdvancedFilterªºµ²ªG¨ú¥N±¼¤F??   
   
2.
            .Replace Rng.Cells(i), "=EX", xlWhole   '¦U¯Z¯Å´«¸m¬°¿ù»~­È
            With .SpecialCells(xlCellTypeFormulas, xlErrors)
                .Value = Rng.Cells(i)              '¿ù»~­È´«¸m¦^¬°¦U¯Z¯Å
            End With
     ³o´X¦æªº¥Øªº¦ü¥G¬O±N¦U¯Z¯Åªº¦~¬ö­È¬D¥X¨Ó«á, ¦A¹ï¨ä¨úMAX¸òMIN
     ¦³ª¦¨ìª©¥D¤§«eªº¤å³¹¹ïSpecialCells¤]³£¦³Ãþ¦üªº¥Îªk..
     ¦ý¬Ý¤F¥b¤Ñ¸ò¤Wºô·j´MÁÙ¬OµLªk§¹¥þ´x´¤¥¿½Tªº¥Îªk...
     ·Q½Ð°Ýªº²Ä¤G­Ó°Ñ¼Æªº xlErrors¬O¦p¦ó§P©w¬°¿ù»~ªº©O?  ¬°¤°»ò¨ú¥N¬°"=EX"  ´N¬O¿ù»~­È©O?
     xlCellTypeFormulas¤£¬Oªí¥Ü"¨Ï¥Î¤½¦¡ªºÀx¦s®æ"¶Ü??


¥H¤W¨â­Ó°ÝÃD¦A³Â·Ð±z¼·ªÅ¸Ñ´b¤F!!  ÁÂÁÂ~~~!

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD