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

[µo°Ý] ¤½¦¡Âà¤Æ¬°µ{¦¡»yªk¡C

¦^´_ 9# joblyc017

J¤j:
¤£¦n·N«ä¡A¶Q¸Ñµª¥i§_§ï¬°¥H¨ç¼Æ¤½¦¡Âà¤Æ¬°µ{¦¡»yªk§@¸Ñ¡A¥H²Å¦X5#ªº­ìÃD·N¡C
ÁÂÁ±z^^

°Ñ¦Ò¤½¦¡¡J©ñ¦bDT1
="DK"&MATCH($DK$4,INDIRECT("DK7:DK"&COUNT(DK:DK)+3),)+6

§Ú¦Û¤v¦³¸Õ¼g¦h¦¸¡A¦ý³£¨S¦³¦¨¥\^^///
·Ð½Ð¤£§[«ü¥¿¡C·P®¦!

TEST-2.rar (12.91 KB)

TOP

¦^´_ 11# papaya
°Ñ¦Ò¬Ý¬Ý,
¤£À´ªºVBAµ{¦¡½X,¥i±N¤£À´ªº¨ç¼Æ,¤èªk,¦bVBA»¡©ú¤W¬d¬Ý¦p Application,©Î Rows
  1. Option Explicit
  2. Private Sub CommandButton1_Click()
  3.     Dim Rng As Range, M As Integer
  4.     Set Rng = Range("DK7", [DK7].End(xlDown))
  5.     M = Application.Max(Rng)
  6.     M = Application.Match(M, Rng, 0)
  7.     [DT1] = Rng.Cells(M).Address(0, 0)
  8.     With Rng.Resize(, 3)
  9.         .Interior.ColorIndex = xlNone
  10.         .Rows(M).Interior.ColorIndex = 8
  11.         [dk4].Resize(, 3) = .Rows(M).Value
  12.     End With
  13. End Sub
  14. Private Sub CommandButton2_Click()
  15.     Dim Rng As Range, M As Integer
  16.     Set Rng = Range("DK7", [DK7].End(xlDown)).Resize(, 3)
  17.     For i = 1 To Rng.Columns.Count
  18.         '*** ·j´M½d³ò¤ºªº¨C­ÓÄæ¦ìªº³Ì¤j­È ****
  19.         With Rng.Columns(i)
  20.             'M = Application.Max(.Cells)
  21.             M = Application.Match(Application.Max(.Cells), .Cells, 0)
  22.             [DT1].Offset(i - 1) = .Cells(M).Address(0, 0)
  23.         End With
  24.         With Rng
  25.             If i = 1 Then .Interior.ColorIndex = xlNone
  26.             .Rows(M).Interior.ColorIndex = Array(, 8, 15, 22)(i)
  27.             [dk4].Offset(i - 1).Resize(, 3) = .Rows(M).Value
  28.         End With
  29.     Next
  30. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2017-11-23 09:59 ½s¿è

¦^´_ 12# GBKEE

¶W¯Åª©¥D:±z¦n!
·PÁ±z¼·¤¾¸Ñµª¡C
¶Q¸Ñµª¡A¤p§Ì·|²Ó¤ß¥J²Óªº¬ã¨s~·P®¦^^

¤p§Ì¤~­è¾ÇVBA¡A³s«Å§iÅܼƩw¸q³£ÁÙ¨S¦³·dÀ´¡A¦ý¤S¥²¶·­n°¨¤W¥Î¡F
ÁöµM¦­´N¦³¬Ý·L³nªº¬ÛÃö¨ç¼Æ¨Ï¥Î¦bVBAªº»¡©ú¡A¦ýÁ`ı±oÁ}ÀßÃøÀ´¡F
¤S·Q¨ì¤@ª½¤W½×¾Â³Â·Ð¤H¡A¤]¤£¬O¿ìªk¡A©Ò¥H¾¨¶q¥ý¤Wºô§ä¬Ûªñªºµ{¦¡»yªk®M¥Î¡C
¤é«e¬Ý¨ì¥i¥H§Q¥ÎApplication±N¤½¦¡Âର»yªk¡A¤]¤£¥Î¦A¿ï¾Ü¶K¤W­È¡A¦]¦¹´N¤@ª½´ú¸Õ¦U¨ç¼Æ¤¤¡A
¦³­þ¨Ç¨ç¼Æ¯à§Q¥ÎApplicationÂà¤Æ? ¨Ã¤Wºô¨D±Ð¥¿½Tªº¼gªk¡C

«Ü¥©~­è­èÂsÄý¨ì¤@½g³s°}¦C¤½¦¡³£¥i¥H¤£¥Î¿ï¾Ü¶K¤W­Èªº¤å³¹¡J
http://forum.twbts.com/viewthread.php?tid=2122&extra=&page=1
5#
¦]¬°¸ÓÃDªº´£°ÝªÌ©M¦^µªªÌ³£¤w³\¤[¥¼µn¿ý½×¾Â¡A©Ò¥H¥¿¦nÂǦ¹½Ð±Ð±z¡J·q½Ð¼·¤¾´f¤©½ç±Ð~
¤£ª¾¬O§_¯à¥H~
X = ["DK"&MATCH($DK$4,INDIRECT("DK7:DK"&COUNT(DK:DK)+3),)+6]
Range(X).Resize(1, 3).Interior.ColorIndex = 8
(¥H¤W¥u¬OÁ|¨Ò)
¤§Ãþ¦üµ{¦¡½X¨Ó§¹¦¨»Ý¨D?
¦pªG¥i¥H¡A½Ð«ü¾É¥¿½Tªº¼gªk¡F
¦pªG¤£«¬~¥ç·q½Ð§iª¾¡C
ÁÂÁ±z^^

TOP

¥»©«³Ì«á¥Ñ joblyc017 ©ó 2017-11-23 10:05 ½s¿è

¦^´_ 11# papaya

test2.gif

TOP

¦^´_ 14# joblyc017
J¤j:±z¦n!
·PÁ±z¼·¤¾¦A¦¸«ü¾É¡C

¥i§_³Â·Ð±z±Nµ{¦¡½|¥t¥~¶K¤W¡C
¹ÏÀɵLªk½Æ»s¡C
ÁÂÁ±z^^

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2017-11-24 07:41 ½s¿è

¦^´_ 12# GBKEE


"¤£À´ªºVBAµ{¦¡½X,¥i±N¤£À´ªº¨ç¼Æ,¤èªk,¦bVBA»¡©ú¤W¬d¬Ý¦p Application,©Î Rows"

GBKEE ¶W¯Åª©¥D:±z¦n!
·PÁ±zªº´£¿ô~­«·s¦A¬d¾\¤@¦¸¡FÁöµM¨S¦³°¨¤W»â·|¡A¦ý¸g¹L¤@¦A®M¨Ò¤l´ú¸Õ¡A²×©óÀ´±o¦p¦ó¹B¥Î^^
µ{¦¡½X«D±`µu§Y¥i
  1. Private Sub CommandButton1_Click()
  2. Range("DK7:DM" & [DM65536].End(xlUp).Row).Interior.ColorIndex = -4142
  3. Range("DK" & Application.Match(Range("DK4"), Range("DK7:DK" & Sheets("Sheet1").[DK65536].End(xlUp).Row), 0) + 6).Resize(1, 3).Interior.ColorIndex = 8

  4. End Sub
½Æ»s¥N½X
TEST-2A.rar (13.51 KB)

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2017-11-24 07:56 ½s¿è

¦^´_ 14# joblyc017

J¤j:
°£¤F¤@­Ó¦r¤@­Ó¦r­«·sºVÁä(¯uªº¤£¯à±µ¨ü¥²¶·³o¼Ë°µ)¡A¹ê¦b¤£ª¾¦p¦ó½Æ»s¶Q¸Ñµªµ{¦¡½X~
©Ò¥H¥¼¯à´ú¸Õ¶Q¸ÑµªÀɪºµ²ªG~©|½Ð¨£½Ì!
¦A¦¸·PÁ±zªº¤£§[«ü¾É^^

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-11-24 08:44 ½s¿è

¦^´_ 16# papaya

¦h¸Õ¸Õ¬Ý«ü©wª«¥óÅܼÆ,¥i´î¤Övba½s¼gªº¿ù»~
  1. Option Explicit
  2. Private Sub CommandButton1_Click()
  3.     Dim Rng As Range, M As Variant
  4.     Dim Sh As Worksheet
  5.     Set Sh = Sheets("sheet1")
  6.     Set Rng = Sh.Range("DK7:DM" & Sh.[DM65536].End(xlUp).Row)
  7.         With Rng
  8.             .Interior.ColorIndex = -4142
  9.             M = Application.Match(Sh.Range("DK4"), .Columns(1), 0)
  10.             If IsNumeric(M) Then    '¦³§ä¨ì
  11.                 .Cells(M, 1).Resize(1, 3).Interior.ColorIndex = 8
  12.             End If
  13.         End With
  14.     'Range("DK7:DM" & [DM65536].End(xlUp).Row).Interior.ColorIndex = -4142
  15.     'Range("DK" & Application.Match(Range("DK4"), Range("DK7:DK" & Sheets("Sheet1").[DK65536].End(xlUp).Row), 0) + 6).Resize(1, 3).Interior.ColorIndex = 8

  16. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 18# GBKEE

¦h¸Õ¸Õ¬Ý«ü©wª«¥óÅܼÆ,¥i´î¤Övba½s¼gªº¿ù»~


GBKEE ¶W¯Åª©¥D:±z¦n!
±zªº¼Ð·Ç½d¨Ò¡A¤p§Ì·|¥J²Ó¬ãŪ~·P®¦

ÁÂÁ±z¤@¦Aªº·ÅÄÉ¥mÀ{¡C
­è­è¤w¦³¤Wºô­q¤F1¥»~¸ò§Ú¾ÇExcel VBA
§Æ±æ¦³©ÒÀ°§U

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD