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

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

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

¥»©«³Ì«á¥Ñ papaya ©ó 2017-11-19 19:34 ½s¿è
  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
½Æ»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)

¥»©«³Ì«á¥Ñ papaya ©ó 2017-11-21 11:38 ½s¿è

ª¦¤å³\¤[~¸Ñ¨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«ü¾É!ÁÂÁÂ¥ý!

TOP

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2017-11-22 05:25 ½s¿è

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

SHEET1.png
2017-11-22 05:17


SHEET2.png
2017-11-22 05:21


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

TOP

¦^´_ 5# papaya
SHEE2ªº¼Ðªº Äæ§Ñ¤F¼Ð¥Ü~¸É¤W
SHEET2.png
2017-11-22 05:40

TOP

¦^´_ 6# papaya


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

TOP

¦^´_ 7# joblyc017

½d¨ÒÀÉ
TEST.rar (5.29 KB)
½Ð°Ñ¦Ò!ÁÂÁ±z^^

TOP

¦^´_ 8# papaya


   
VBA.png
2017-11-22 12:24

TOP

¦^´_ 9# joblyc017

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

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD