[µ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

ª¦¤å³\¤[~¸Ñ¨M¤F~C2
[C2] = Application.Index(Range("D:D"), Application.Match(Range("A2"), Range("I:I"), 0))

¦ýC1ÁÙ¬OµLªk¸Ñ¨MQQ
https://support.microsoft.com/zh-tw/help/291308/how-to-select-cells-ranges-by-using-visual-basic-procedures-in-excel
¦]¬°¹ê¦bµLªk¥Ñ¤W­zºô¯¸ªº»¡©ú¤Î½d¨Ò¤¤§¹¥þ²z¸Ñ©M®M¥Î¦bC1¤½¦¡

Àµ½Ð¦U¦ì°ª¤â¤£§[½ç±Ð©M«ü¾É!ÁÂÁÂ¥ý!

joblyc017

¦^´_ 2# papaya

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))

½Æ»s¥N½X

papaya

¦^´_ 3# joblyc017
ÁA¸Ñ¤F~
,,4  ­ì¨Ó4Ä檺½d³ò¥²¶·¥H .Resize(, 4)¨Óªí¹F¡C
ÁÂÁ±z^^

papaya

¦^´_ 3# joblyc017
j¤j:
¤£¦n·N«ä¡A¯à§_¦A½Ð±z«ü¾É~
·í"DK7:DK"&COUNT(DK:DK)+3¤§½d³ò¤º=$DK$4ªº¦C¡A¨ä¸Ó¦C·íÄæ©M¥kÃ䪺¤GÄæ¼Ð¥Ü8¸¹©³¦â¡C
½Ð°Ý:¤½¦¡Âà¤Æ¬°µ{¦¡»yªk­n¦p¦ó½s¼g?

¥t~
±N"DK7:DK"&COUNT(DK:DK)+3¤§½d³ò¤º=³Ì¤j¼Æªº¦C¡A¨ä¸Ó¦C·íÄæ©M¥kÃ䪺¤GÄæ¼Ð¥Ü8¸¹©³¦â¡C
½Ð°Ý:¤½¦¡Âà¤Æ¬°µ{¦¡»yªk­n¦p¦ó½s¼g?
ÁÂÁ±z^^

PS: "DK7:DK"&COUNT(DK:DK)+3ªº­È¡A¤£·|­«½Æ¡A¤¤¶¡¤]¤£·|¦³ªÅ¥Õ®æ¡C

2017-11-22 05:17

2017-11-22 05:21

¥u­nSHEET1§Y¥i¡ASHEET2¥u§@»²§U»¡©ú¥Î¡C

papaya

¦^´_ 5# papaya
SHEE2ªº¼Ðªº Äæ§Ñ¤F¼Ð¥Ü~¸É¤W

2017-11-22 05:40

joblyc017

¦^´_ 6# papaya


    ½Ð´£¨Ñ½d¨ÒÀÉ¡AÁÂÁÂ

papaya

¦^´_ 7# joblyc017

½d¨ÒÀÉ
TEST.rar (5.29 KB)

¤U¸ü¦¸¼Æ: 5

2017-11-22 11:59

½Ð°Ñ¦Ò!ÁÂÁ±z^^

joblyc017

¦^´_ 8# papaya


   

2017-11-22 12:24

papaya

¦^´_ 9# joblyc017

J¤j:
ÁÂÁ±zªº¤£§[«ü¾É^^