[已解决]用代码对多列求和

梦想有多远

用代码对多列求和
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For x = 3 To 100 '列
For Y = 3 To 1000 '行
Cells(2, x) = [=Sum(Cells(Y, x))]
Next
Next
'   [B2] = [=SUM(B3:B1000)]
'   [C2] = [=SUM(C3:C1000)]
'   [D2] = [=SUM(D3:D1000)]
'   [E2] = [=SUM(E3:E1000)]
'   [F2] = [=SUM(F3:F1000)]
'   [G2] = [=SUM(G3:G1000)]
'   [H2] = [=SUM(H3:H1000)]
'   [I2] = [=SUM(I3:I1000)]
'   [J2] = [=SUM(J3:J1000)]

End Sub

最佳答案

月排行榜 / 总排行榜

dsmch

2015-12-26 17:44

1. Sub Macro2()
   
2. [b2] = "=SUM(B3:B1000)"
   
3. Range(Cells(2, 2), Cells(2, 100)).FillRight '100列
   
4. End Sub

复制代码

跳转到最佳答案楼层

shwanghuafeng

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For x = 3 To 100 '列
  For Y = 3 To 1000 '行
    Cells(2, x) = application.sum(range(cells(3,x),cells(1000,x)))
  Next
Next
End Sub
是这样吗……

dsmch

1. Sub Macro2()
   
2. [b2] = "=SUM(B3:B1000)"
   
3. Range(Cells(2, 2), Cells(2, 100)).FillRight '100列
   
4. End Sub

复制代码

梦想有多远

dsmch 发表于 2015-12-26 17:44

谢谢老师的解答，效果非常好，比循环语句要快多了

梦想有多远

shwanghuafeng 发表于 2015-12-26 17:44
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For x = 3 To 100 '列
  For Y = 3 To 1 ...

谢谢老师的解答，达到了我要的效果，循环语句反映有点慢，楼下的代码要快很多

shwanghuafeng

梦想有多远 发表于 2015-12-26 18:15
谢谢老师的解答，达到了我要的效果，循环语句反映有点慢，楼下的代码要快很多

我也是刚学的菜鸟，楼上老师写的比我好多啦{:27:}