converting references from static to dynamic

[Luke777]

Hi all,

I've been using the below to place a formula in V2 and autofill it to my last Column.

With ws
.Cells(1, lCol).Offset(1, 0).FormulaR1C1 = _

"=IF(RC[-4]-RC[-6]<0, ""-"" & TEXT(ABS(RC[-4]-RC[-6]),""d hh:mm:ss""), RC[-4]-RC[-6])"

.Cells(1, lCol).Offset(1, 0).AutoFill Destination:=.Range("V2:V" & lRow) 

.Range("V2:V" & lRow).NumberFormat = "[$-x-systime]d h:mm:ss AM/PM"
end ws

.Cells(1,lCol) refers to V1, the offset makes it V2. This part is already dynamic, but I'm not sure how I'd go about using the same process for

.Range("V2:V" & lRow)

Is it going to be something like

.Range(cells(1,lCol).offset(1,0) &":" &cells(lRow,lCol))

? No idea how close that is

Thanks for any help

 

[Joe4]

You do not need to use autofill in VBA, if you have already calculated the last row you need to go down to. Just apply the formula to the whole range at once, i.e.

.Range("V2:V" & lRow).FormulaR1C1 = _
    "=IF(RC[-4]-RC[-6]<0, ""-"" & TEXT(ABS(RC[-4]-RC[-6]),""d hh:mm:ss""), RC[-4]-RC[-6])"

 

[Luke777]

You do not need to use autofill in VBA, if you have already calculated the last row you need to go down to. Just apply the formula to the whole range at once, i.e.

.Range("V2:V" & lRow).FormulaR1C1 = _
    "=IF(RC[-4]-RC[-6]<0, ""-"" & TEXT(ABS(RC[-4]-RC[-6]),""d hh:mm:ss""), RC[-4]-RC[-6])"

ahh okay, that will make things simpler, but how do I convert the below

.Range("V2:V" & lRow)

so that it's dynamic? i.e. changes before what I'm doing might make V no longer the column that I'm using.

I've already got the lCol value, I'm just not sure how to implement it in this scenario

 

[Joe4]

You can use "Cells" in place of "Range", i.e. to go from row 2 down to the last row ("lRow") in the lCol column, you can use this:

.Range(.Cells(2, lCol), .Cells(lRow, lCol)).FormulaR1C1 = _

 

[Luke777]

You can use "Cells" in place of "Range", i.e. to go from row 2 down to the last row ("lRow") in the lCol column, you can use this:

.Range(.Cells(2, lCol), .Cells(lRow, lCol)).FormulaR1C1 = _

Thank you! This was the solution I was wondering about in my first post I'm glad I wasn't too far off! But thank you for getting it over the line

 

[Joe4]

You are welcome!

That is one reason why I usually prefer using "Cells" over "Range" when dealing with variable column references.
"Cells" can accept the index number of the column, where "Range" requires the column letter, which is harder to get.