Modify formula to apply to range row 2 to the last row in a column

[abberyfarm]

Hi,

I recorded the macro below which applies a formula to range in Col C.

Would anybody know how I can modify it to apply the formula down as far as the last used row in Col B? As the formula uses values from Column B.

Appreciate any help

Thanks

Range("C2").Select
    Selection.FormulaArray = _
        "=IF(LEFT(TEXT(RC[-2],""dd/mm/yyyy hh:mm""),13)=LEFT(TEXT(R[1]C[-2],""dd/mm/yyyy hh:mm""),13),"""",AVERAGE(IF(LEFT(TEXT(R2C1:R[COLOR=#00ff00]10463[/COLOR]C1,""dd/mm/yyyy hh:mm""),13)=LEFT(TEXT(RC[-2],""dd/mm/yyyy hh:mm""),13),R2C2:R[COLOR=#00ff00]10463[/COLOR]C2)))"
    Selection.AutoFill Destination:=Range("C2:C[COLOR=#00ff00]10463[/COLOR]")

 

[VoG]

Try

Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("C2").FormulaArray = _
        "=IF(LEFT(TEXT(RC[-2],""dd/mm/yyyy hh:mm""),13)=LEFT(TEXT(R[1]C[-2],""dd/mm/yyyy hh:mm""),13),"""",AVERAGE(IF(LEFT(TEXT(R2C1:R10463C1,""dd/mm/yyyy hh:mm""),13)=LEFT(TEXT(RC[-2],""dd/mm/yyyy hh:mm""),13),R2C2:R10463C2)))"
Range("C2").AutoFill Destination:=Range("C2:C" & LR)

 

[abberyfarm]

Thanks for you help.

I also need to change the "R10463C1" part of the code to go down to the lastrow in column A but not sure how you do this in the R1C1 format?

Thanks

 

[VoG]

Try (untested)

Dim LR As Long, LRA As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
LRA = Range("A" & Rows.Count).End(xlUp).Row
Range("C2").FormulaArray = _
        "=IF(LEFT(TEXT(RC[-2],""dd/mm/yyyy hh:mm""),13)=LEFT(TEXT(R[1]C[-2],""dd/mm/yyyy hh:mm""),13),"""",AVERAGE(IF(LEFT(TEXT(R2C1:R" & LRA & "C1,""dd/mm/yyyy hh:mm""),13)=LEFT(TEXT(RC[-2],""dd/mm/yyyy hh:mm""),13),R2C2:R" & LRA & "C2)))"
Range("C2").AutoFill Destination:=Range("C2:C" & LR)