请问在VBA中，使用SUM函数时，行为变量怎么办？

dzw

现有一问题，求第8列（H列）第N行到M行的合计值(N,M为变量)，要求在VBA中用excel自带函数SUM求和，怎么办？代码怎么编？

山菊花

Sum = WorksheetFunction.Sum(Range("h" & n & ":h" & m))

或
Sum = WorksheetFunction.Sum(Range(Cells(n, 8), Cells(m, 8)))

或
Sum = WorksheetFunction.Sum(Range(Cells(n, "h"), Cells(m, "h")))

northwolves

Sub 宏1()
Dim M, N, TEMP
TEMP = InputBox("请依次输入M,N的值，用逗号隔开", "提示", "5,10")
M = Trim(Split(TEMP, ",")(0))
N = Trim(Split(TEMP, ",")(1))
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R" & M & "C8:R" & N & "C8)"
MsgBox "第8列第 " & M & " 行到第 " & N & " 行的和为 " & Cells(1, 1)
Cells(1, 1) = ""
End Sub

学习分享

好东西，我也学到了！对RANGE的变量引用！我最想要的了！

zhiyong_5833

如果想对一不定区域限定单元格格式(如第一列有n行,便是想对A1:An规定格式),该如何编代码?下面代码中用的是固定值,不知应如何修改?

Range("A1:C9").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    With Selection.Font
        .Name = "新宋体"
        .FontStyle = "加粗"
        .Size = 16
    End With

plxmm

Range("A1:C" & n).Select

wcygr

Sub 设置单元格格式() '对任意不定区域限定单元格格式

Dim TEMP
Dim M As Byte, X As Byte, N As Long
On Error Resume Next
TEMP = InputBox("请依次输入列号数X,起行M,止行N的值，格式如下", "提示", "3,1,25")
X = Trim(Split(TEMP, ",")(0)) '列号数

M = Trim(Split(TEMP, ",")(1)) '起行
N = Trim(Split(TEMP, ",")(2)) '止行
Range(Cells(M, X), Cells(N, X)).Select '选择区域
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    With Selection.Font
        .Name = "新宋体"
        .FontStyle = "加粗"
        .Size = 16
    End With
MsgBox "第" & X & "列第 " & M & " 行到第 " & N & " 行的单元格格式为：新宋体并加粗"
End Sub

zhiyong_5833

QUOTE:

以下是引用plxmm在2006-7-24 9:16:16的发言：
Range("A1:C" & n).Select

谢谢,尝试了一下问题解决了

GreaterSnow

求助：

在VB中怎样实现在excel中counta（）的统计功能，也就是在excel中counta（）可以统计非空单元格个数，那么在VB中怎样实现？

smile009

感谢各位，真是一语点醒梦中人