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

papaya

1. Private Sub CommandButton1_Click()
   
2. '[A2] = "=Max(D:D)"
   
3. '[A2] = [A2].Value
   
4. [A2] = Application.Max(Range("D:D"))
   
5.   [B1] = [I2]
   
6.   [B2] = [D2]
   
7.   [C2] = "=INDEX(D:D,MATCH(A2,I:I,))"
   
8.   [C2] = [C2].Value
   
9.   [C1] = "=SUMIF(OFFSET($I$1,C2-B2+1,1,,4),A1,OFFSET($D$1,C2-B2+1,1,,4))"
   
10.   [C1] = [C1].Value
    
11. 
    
12. End Sub

½Æ»s¥N½X

A1=¤H¤u¶ñ¤J

EX¡J
'[A2] = "=Max(D:D)"
'[A2] = [A2].Value
¥iÂà¤Æ¬°
[A2] = Application.Max(Range("D:D"))

½Ð°Ý¡J
[C2]©M[C1]­n¦p¦óÂà¤Æ?

PS:Sheet2©MSheet3¥u§@»²§U»¡©ú¥Î¡F§¹¦¨«á´N²¾°£¡C

¥ý·PÁ¦U¦ì¤j¤jªº¼ö¤ß¨ó§U¡C
12¥Í¨v-VBA-Q.rar (10.03 KB)

¤U¸ü¦¸¼Æ: 7

2017-11-19 19:34

papaya

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

GBKEE

¦^´_ 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. 
    
17. End Sub

½Æ»s¥N½X

papaya

¦^´_ 14# joblyc017

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

papaya

¦^´_ 12# GBKEE

¤U¸ü (153.29 KB)

2017-11-24 07:37

"¤£À´ªº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. 
   
5. End Sub

½Æ»s¥N½X

TEST-2A.rar (13.51 KB)

¤U¸ü¦¸¼Æ: 2

2017-11-24 07:39

papaya

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

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

joblyc017

¦^´_ 11# papaya

¤U¸ü (359.41 KB)

2017-11-23 10:05

papaya

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

GBKEE

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

papaya

¦^´_ 9# joblyc017

¤U¸ü (162.2 KB)

2017-11-23 02:47

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)

¤U¸ü¦¸¼Æ: 3

2017-11-23 02:48