前端之家

  1. 首页
  2. 问答

求和动态范围 Excel VBA

• 问答

我正在尝试对不固定的整个列中的值求和。

Sub CTG()
Dim sht As Worksheet
Dim PreviousData As Long
Dim MySum As Double,ColLetter As String

Set sht = ThisWorkbook.Worksheets("Weekly")
PreviousData = sht.Cells(2,sht.Columns.Count).End(xlToLeft).Column - 1

ColLetter = Split(Cells(1,PreviousData).Address,"$")(1)
 

MySum = Application.WorksheetFunction.Sum(sht.Range("C:C"))

'MsgBox "The total of the ranges is " & MySum

End Sub

我想做的是转换

MySum = Application.WorksheetFunction.Sum(sht.Range("C:C"))

进入

MySum = Application.WorksheetFunction.Sum(sht.Range("ColLetter :ColLetter"))

我尝试了多种方法，但似乎不起作用。

任何帮助将不胜感激。谢谢

eryaa 回答：求和动态范围 Excel VBA

一列的总和

  • 下面对第二行计算的最后一个非空列之前的列中的值求和。
  • 你必须知道Columns("C") = Columns(3) = Range("C:C")。
Option Explicit

Sub SumOfColumn()
    
    Dim MySum As Double
    With ThisWorkbook.Worksheets("Weekly")
        Dim blCol As Long ' Column Before the Last Column
        blCol = .Cells(2,.Columns.Count).End(xlToLeft).Column - 1
        If blCol = 0 Then
            MsgBox "There is no column before column ""A"".",_
                vbCritical,"Sum of Column"
            Exit Sub
        End If
        MySum = Application.Sum(.Columns(blCol))
    End With
    
    MsgBox "The column's total is '" & MySum & "'.",_
        vbInformation,"Sum of Column"

End Sub
  • BigBen 在评论中解决了这个问题（使用 sht.Columns(PreviousData)，例如 sht.Columns(3)）。
excelvba
本文链接：https://www.f2er.com/1129.html

大家都在问