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

pcwh3

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

¤U¸ü¦¸¼Æ: 22

2016-9-3 11:24

c_c_lai

¦^´_ 1# pcwh3
¬O³o¼Ë»ò¡H

1. Sub test20160903()
   
2.     Dim lc As Integer
   
3. 
   
4.     lc = [bz1].End(xlToLeft).Column
   
5.     '  MsgBox lc
   
6. 
   
7.     [a1] = "=sum(" & ExChg(1, lc - 10) & ", " & ExChg(1, lc - 1) & ")"
   
8.     [a2] = "=sum(b2:" & ExChg(2, lc - 1) & ")"
   
9. End Sub
   
10. 
    
11. Function ExChg(r As Integer, c As Integer) As String
    
12.     ExChg = Chr(64 + c) & CStr(r)
    
13. End Function

½Æ»s¥N½X

c_c_lai

¦^´_ 1# pcwh3

¤U¸ü (33.36 KB)

2016-9-3 14:35

pcwh3

¤U¸ü (7.31 KB)

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

c_c_lai

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

c_c_lai

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

­ã´£³¡ªL

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

c_c_lai

¦^´_ 7# ­ã´£³¡ªL
ÁÂÁª©¤jªº«ü¾É¡A¤SÁA¸Ñ¤F¥t¤@ºØªºªí¥Ü¤è¦¡¡A
¯u¦hÁ¡I

¤U¸ü (54.7 KB)

2016-9-3 17:32

pcwh3

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

­ã´£³¡ªL

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