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

VBA¤¤¤½¦¡ÂàÅÜ¥X¿ù°ÝÃD

VBA¤¤¤½¦¡ÂàÅÜ¥X¿ù°ÝÃD

¦³¤@¨Ç¦bVBA¤¤ªºµ{¦¡¼gªk¥X¿ù°ÝÃD¦pªþ¥ó¡A§Æ±æ¦U¦ì¥i¥HÀ°¦£«üÂI¤@¤U»Ý¦p¦ó§ó§ï¡C
ÁÂÁ¡C test20160903.zip (8.39 KB)

¦^´_ 1# pcwh3
¬O³o¼Ë»ò¡H
  1. Sub test20160903()
  2.     Dim lc As Integer

  3.     lc = [bz1].End(xlToLeft).Column
  4.     '  MsgBox lc

  5.     [a1] = "=sum(" & ExChg(1, lc - 10) & ", " & ExChg(1, lc - 1) & ")"
  6.     [a2] = "=sum(b2:" & ExChg(2, lc - 1) & ")"
  7. End Sub

  8. Function ExChg(r As Integer, c As Integer) As String
  9.     ExChg = Chr(64 + c) & CStr(r)
  10. End Function
½Æ»s¥N½X

TOP

¦^´_ 1# pcwh3
E1.png
2016-9-3 14:35

TOP

¥»©«³Ì«á¥Ñ pcwh3 ©ó 2016-9-3 15:22 ½s¿è

¹Ï¤ù 3.jpg
2016-9-3 15:19
¦^´_ 2# c_c_lai

­è¸Õ¹L¡C¦ý¦pªG§Ú±NLC¦ì¸m§ó§ï¨ìBA(¨Ò¦p)¡A¹B¦æMACRO¤§«áªºµ²ªG¬O¿ùªº¡C
³o¬O¬Æ»ò­ì¦]¡C
¦bA2¤¤À³¬°=sum(b2:az2)¤~¬O¥¿½T¡C

TOP

¦^´_ 4# pcwh3
§A¬O»¡±N lc = [bz1].End(xlToLeft).Column
§ï¦¨ lc = [ba1].End(xlToLeft).Column ?
¨äµ²ªG¬O¤@¼Ëªº¡C

TOP

¦^´_ 4# pcwh3
§Aªº [A2] ¤½¦¡¬°¡G
[a2] = "=sum(b2: cells(2,lc-1))"
«ç¥i¯à [a2] = =sum(b2:az2)¤~¬O¥¿½T???

TOP

[a1] = "=sum(" & Range(Cells(1, lc - 11), Cells(1, lc - 1)).Address & ")"
[a2] = "=sum(b2:" & Cells(2, lc - 1).Address(0, 0) & ")"

TOP

¦^´_ 7# ­ã´£³¡ªL
ÁÂÁª©¤jªº«ü¾É¡A¤SÁA¸Ñ¤F¥t¤@ºØªºªí¥Ü¤è¦¡¡A
¯u¦hÁ¡I
E4.png
2016-9-3 17:32

TOP

¦^´_ 7# ­ã´£³¡ªL

¦hÁÂÀ°¦£¡C¹êÃÒ¥N¤J§Ú¯u¹ê¹B§@ªºmacro ¤¤¡A¾ã­Ó¹B¦æºZ¶¶§Ö±¶¡C
¦ý¹ï§A©Ò´£ªºaddress. (0,0) ¤£¬Æ©úÁA­ì²z¡C
¥i¥Hªº¸Ü¥i§_Á¿¸Ñ¤@¤U¡A©Î´£¨Ñ°Ñ¦Ò¸ô®|¡C
ÁÂÁ¡C

TOP

¦^´_ 9# pcwh3

¬Ý¤@¤U
MsgBox Range("A1:F5").Address
MsgBox Range("A1:F5").Address(0, 0)
MsgBox Range("A1:F5").Address(1, 0)
MsgBox Range("A1:F5").Address(0, 1)

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD