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. End Sub

A1=人工填入

EX︰
'[A2] = "=Max(D:D)"
'[A2] = [A2].Value

[A2] = Application.Max(Range("D:D"))

[C2]和[C1]要如何轉化?

PS:Sheet2和Sheet3只作輔助說明用；完成後就移除。

[attach]27994[/attach]

[C2] = Application.Index(Range("D:D"), Application.Match(Range("A2"), Range("I:I"), 0))

C1還是無法解決QQ
https://support.microsoft.com/zh-tw/help/291308/how-to-select-cells-ranges-by-using-visual-basic-procedures-in-excel

1. [C1] = Application.SumIf(Range("I1").Offset(Range("C2") - Range("B2") + 1, 1).Resize(, 4), Range("A1"), Range("D1").Offset(Range("C2") - Range("B2") + 1, 1).Resize(, 4))

,,4  原來4欄的範圍必須以 .Resize(, 4)來表達。

j大:

PS: "DK7:DK"&COUNT(DK:DK)+3的值，不會重複，中間也不會有空白格

[attach]28005[/attach]

[attach]28006[/attach]

SHEE2的標的 欄忘了標示~補上
[attach]28007[/attach]

請提供範例檔，謝謝

[attach]28008[/attach]

[attach]28009[/attach]

J大:

[attach]28013[/attach]
J大:

="DK"&MATCH(\$DK\$4,INDIRECT("DK7:DK"&COUNT(DK:DK)+3),)+6

[attach]28014[/attach]

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)
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.         '*** 搜尋範圍內的每個欄位的最大值 ****
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

5#

X = ["DK"&MATCH(\$DK\$4,INDIRECT("DK7:DK"&COUNT(DK:DK)+3),)+6]
Range(X).Resize(1, 3).Interior.ColorIndex = 8
(以上只是舉例)

[attach]28019[/attach]

J大:您好!

[attach]28021[/attach]

"不懂的VBA程式碼,可將不懂的函數,方法,在VBA說明上查看如 Application,或 Rows"

GBKEE 超級版主:您好!

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

[attach]28022[/attach]

J大:

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

GBKEE 超級版主:您好!

 歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)