VBA Use declared variable as part of a formula

[Chris123149]

Hi All,

I was hoping somebody here could help me out with a particular line of code that i can't seem to get right. I'm looking to insert an "=average" code when a macro is run,, but the column index of each end of the range can vary depending on the active row. The actual code reads:

yrow = ActiveCell.Row


Cells(yrow, 34).Formula = "=average(range(cells(yrow,2).value):cells(yrow, 32).value)"

but keeps giving me an #name ? error in cell 34. What do i need to change to fix this?

 

[FormR]

Hi, maybe something like this.

Cells(YROW, 34).Formula = "=average($B$" & YROW & ":$AF$" & YROW & ")"

 

[Chris123149]

Hi, maybe something like this.

Cells(YROW, 34).Formula = "=average($B$" & YROW & ":$AF$" & YROW & ")"

Great! Thats exactly what im looking for. Many thanks

 

[RoryA]

Alternatively, since it's the same row, it can be easier to use R1C1 references:

Cells(yrow, 34).FormulaR1C1 = "=average(RC2:RC32)"