對于任何了解 VBA 世界的人來說，這可能是一個非常簡單的問題，但我是一個完全的初學者，幾天來我一直在處理以下（盡管微不足道的）問題，但我無法解決它
我正在從另一個 excel 中繪制資料集，所以我永遠不知道結果表有多大。我需要將列中的值匯總到一行中，并將行中求和的公式動態擴展到最后一個填充的列。我能夠找到黃色欄位，但我不知道如何使用 VBA 將公式動態填充到紅色欄位中。
我知道我在下面自動填充的嘗試沒有也不會奏效，但我想不出其他任何東西。
感謝您的任何提示
Sub IN7()
lr = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
lc = Cells.Find("*", Cells(1, 1), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
Range("B" & (lr 2)).Value = "=sum(B2:B" & lr & ")"
Range("B" & (lr 2)").AutoFill Range("B" & (lr 2)" & lc)
End Sub
uj5u.com熱心網友回復：
絕對不是專業人士，但這可能會解決您的問題。
Sub SumAllColumns()
Dim a As Integer
Range("B7").Select
ActiveCell.FormulaR1C1 = "= SUM(R[-5]C:R[-2]C)"
Range("B7").Select
a = Sheet1.UsedRange.Columns.Count
Selection.AutoFill Destination:=Selection.Resize(1, a - 1)
End Sub
這假設您有 4 行，并且您的第一個總和必須在單元格 B7 中，按照您的示例。如果行數也是可變的，請告訴我，我會嘗試更改它。
uj5u.com熱心網友回復：
那么您要嘗試的是創建一個自動檢測表的最后一行和最后一列的值，對嗎？如果是這種情況，請使用：
lr = Cells(Rows.Count, 1).End(xlUp).Row 'goes to the last row and then goes up to the last row with any value
lc = Cells(1, Columns.Count).End(xltoLeft).Column 'goes to the last column and then goes left to the last column with any value
希望以前有人教過我這個。
關于總和部分，我不太明白你想做什么。我認為您要做的是將第二行的總和擴展到其余道路，對嗎？為什么不使用回圈？
for i = 2 to lr
cells(i, lc 2).Value = WorksheetFunction.Sum(Range(Cells(i, 1), Cells(i, lc)))
next i
希望能幫助到你！！
uj5u.com熱心網友回復：
添加總計
Option Explicit
Sub AddTotals()
' Reference the worksheet.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Calculate the last row and column.
Dim lr As Long
lr = ws.Cells.Find("*", ws.Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Dim lc As Long
lc = ws.Cells.Find("*", ws.Cells(1), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
' Reference the first column range.
Dim fcrg As Range: Set fcrg = ws.Range("B2", ws.Cells(lr, "B"))
' Write the address of the first column range to a string variable.
' Lock the rows with '(, 0)' so the formula will work for all columns.
Dim fcrgAddress As String: fcrgAddress = fcrg.Address(, 0)
' Reference the first sum cell.
Dim cell As Range: Set cell = ws.Cells(lr 2, "B")
' Calculate the number of columns.
Dim cCount As Long: cCount = lc - cell.Column 1
' Reference the sum row range.
Dim srrg As Range: Set srrg = cell.Resize(, cCount)
' Write the formula to the sum row range.
srrg.Formula = "=SUM(" & fcrgAddress & ")"
End Sub
更少的變數
Sub AddTotalsShort()
' Reference the worksheet.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Calculate the last row and column.
Dim lr As Long
lr = ws.Cells.Find("*", ws.Cells(1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
Dim lc As Long
lc = ws.Cells.Find("*", ws.Cells(1), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
' Write the address of the first column range to a string variable.
Dim fcrgAddress As String
fcrgAddress = ws.Range("B2", ws.Cells(lr, "B")).Address(, 0)
' Reference the sum row range.
Dim srrg As Range
With ws.Cells(lr 2, "B")
Set srrg = .Resize(, lc - .Column 1)
End With
' Write the formula to the sum row range.
srrg.Formula = "=SUM(" & fcrgAddress & ")"
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/534665.html
標籤：擅长VBAexcel公式