dynamic column reference in formula

Question

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)"

Regards,

Answer 1

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'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).

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