I need to reference columns by number in Sum function (i.e. Column B =2, C=3, E=5, etc)
我需要在Sum函数中按数字引用列(即B =2, C=3, E=5等)
i tried this code but it didn't work (ColN is the column number read from a listbox index)
我尝试了这段代码，但它不起作用(ColN是listbox索引中读取的列号)
Range("M5") = WorksheetFunction.Sum(Range(ColN& "2" : ColN&"5"))) '(F2:F5) for example
I prefer also to use formulaR1C1 cos this function needs to be applied for the rest of the rows (i.e the next cell would be the sum of F3:F6 and so on)
我也更喜欢使用公式c1，因为这个函数需要应用于其余的行(I)。e下一个单元格是F3的和F6等等)
All help to solve the problem would be appreciated.
如能解决这个问题，我们将不胜感激。
1 个解决方案
#1
1
You can do it like this:
你可以这样做:
Range("M5") = WorksheetFunction.Sum(Range(Cells(2, 6), Cells(5, 6)))
The Cells functions accepts a row and column parameter, which is similar to what you want for R1C1 notation. In this case, 6 means column 6, i.e. column F. In your case, you have colN as a column number, so you could do this:
单元格函数接受行和列参数，这类似于R1C1符号。在这种情况下，6表示第6列，也就是第f列，在你的情况下，你有colN作为列号，所以你可以这样做:
'Dim colN As Long
'colN = 6 ' read from a listbox index in your case, for example
Range("M5") = WorksheetFunction.Sum(Range(Cells(2, colN), Cells(5, colN)))
#1
1
You can do it like this:
你可以这样做:
Range("M5") = WorksheetFunction.Sum(Range(Cells(2, 6), Cells(5, 6)))
The Cells functions accepts a row and column parameter, which is similar to what you want for R1C1 notation. In this case, 6 means column 6, i.e. column F. In your case, you have colN as a column number, so you could do this:
单元格函数接受行和列参数，这类似于R1C1符号。在这种情况下，6表示第6列，也就是第f列，在你的情况下，你有colN作为列号，所以你可以这样做:
'Dim colN As Long
'colN = 6 ' read from a listbox index in your case, for example
Range("M5") = WorksheetFunction.Sum(Range(Cells(2, colN), Cells(5, colN)))