公式中的动态列引用 [英] dynamic column reference in formula

问题描述

我想在最后一列的左侧放置一个公式3个单元格.有没有办法做到这一点?也许通过列地址?

I'd like to place a formula 3 cells to the left of the last column. Is there a way to do this? Perhaps through a column address?

lc = .Cells(3, Columns.count).End(xlToLeft).Column
.Cells(3, lc + 3).Formula = "=CountCcolor(E3:N" & lrPT & ", " & .Cells(0, lc + 2) & "3)"

此致

推荐答案

lc + 3在右边三列，而不是左边三列，但是几乎可以肯定是错字.这应该使您的公式构造变得平方.

lc + 3 is three columns to the right, not the left but that is almost assuredly a typo. This should square your formula construction away.

lc = .Cells(3, Columns.count).End(xlToLeft).Column
.Cells(3, lc + 3).Formula = "=CountCcolor(E3:N" & lrPT & ", " & .Cells(3, lc + 2).ADDRESS & ")"

VBA的 Range.Address属性可以在以下位置输出单元格引用相对和绝对寻址的各种组合.我将其保留为默认值(例如，绝对值)，但我不认为这实际上对您的公式(单个单元格中的单个公式)很重要.

VBA's Range.Address property can output cell references in various combinations of relative and absolute addressing. I've left this as the default (e.g. absolute) but I do not believe it actually matters in your formula (single formula in single cell).

顺便说一句，如果列N中有值，则lc + 2将引用P3，而不是O3.

btw, if there are values in column N, the lc + 2 will be referencing P3, not O3.

这篇关于公式中的动态列引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！