VBA FOR循环以增加列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40322795/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA FOR loop to increment columns
提问by Ganesh Prasad B K
'Calculating Sum of Max. values of Eth column from pivot table values
Dim lastRowE As Long
lastRowE = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
'Excluding grandtotal
lastRowE = lastRowE - 1
Worksheets("pmrrcconnmax").Activate
Cells(1, 5).Value = WorksheetFunction.SumIfs(Range("E7:E" & lastRowE), _
Range("E7:E" & lastRowE), ">0")
'Calculating Sum of Max. values of Fth column from pivot table values
'Dim lastRowF As Long
lastRowF = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
'Excluding grandtotal
lastRowF = lastRowF - 1
'Worksheets("pmrrcconnmax").Activate
Cells(1, 6).Value = WorksheetFunction.SumIfs(Range("F7:F" & lastRowF), _
Range("F7:F" & lastRowF), ">0")
'Calculating Sum of Max. values of Gth column from pivot table values
'Dim lastRowG As Long
lastRowG = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
'Excluding grandtotal
lastRowG = lastRowG - 1
'Worksheets("pmrrcconnmax").Activate
Cells(1, 7).Value = WorksheetFunction.SumIfs(Range("G7:G" & lastRowG), _
Range("G7:G" & lastRowG), ">0")
and so on.... upto Kth column (since I am new to VBA). Can someone help me to find the way to put them in for loop or any other suitable loop?
等等......直到第 K 列（因为我是 VBA 的新手）。有人可以帮我找到将它们放入 for 循环或任何其他合适循环的方法吗？
Thank you in advance
先感谢您
回答by yd1
if i understood correctly, and you want to use forin VBA and refer to a different cell each time, try this example:
如果我理解正确，并且您想for在 VBA 中使用并每次引用不同的单元格，请尝试以下示例：
Dim row As Integer
Dim col As Integer
For row = 1 To 10
For col = 1 To 10
Cells(row, col).Value = 1
Next
Next
it fills the cells in 10x10 with value of 1.
它以 10x10 的值填充单元格，值为 1。
with Cellsyou can easily insert integer variables to select a cell.
与Cells您可以轻松地插入整数变量来选择一个单元格。
you can also combine it with Rangelike this example:
您也可以将其与Range此示例结合使用：
Range(Cells(1, 1), Cells(5, 5)).Select
you cal also select a whole column like this:
您还可以像这样选择一整列：
Columns(5).Select
回答by Niclas
Try this. It will only loop through from E to K.
尝试这个。它只会从 E 循环到 K。
Option Explicit
Sub Add_Formulas()
'Declaring the variable lColumn as long to store the last Column number
Dim lColumn As Long
'Declaring the variable iCntr as long to use in the For loop
Dim iCntr As Long, iCell As Long
Dim ColLetter As String
Dim lastCol As Long, lastRow As Long
Dim wks As Worksheet
' Set wks so it is the activesheet
Set wks = ThisWorkbook.ActiveSheet
'lastCol = wks.Cells(1, wks.Columns.Count).End(xlToLeft).Column
' Using column D as you are using column no. 4 in your code
lastRow = wks.Range("D" & wks.Rows.Count).End(xlUp).Row
'Excluding grandtotal
lastRow = lastRow - 1
'Assigning the last Column value to the variable lColumn
'lColumn = lastCol
lColumn = 11
'Using for loop
' 5-11 = E-K
For iCntr = lColumn To 5 Step -1
ColLetter = GetColumnLetter(iCntr)
Cells(1, iCntr).Value = WorksheetFunction.SumIfs(Range(ColLetter & "7:" & ColLetter & lastRow), _
Range(ColLetter & "7:" & ColLetter & lastRow), ">0")
Next
End Sub
Function GetColumnLetter(colNum As Long) As String
Dim vArr
vArr = Split(Cells(1, colNum).Address(True, False), "$")
GetColumnLetter = vArr(0)
End Function
