Refer to range in cell formula

[Judi]

Hi all,

Can someone help me with this vba code in excel? It errors but I Can’t figure out why. I want to write a cell formula with a dynamic range. Therefore, I specify this range in the vba code, but it errors every time. Does anyone know how to solve this?

Dim rng1 As Range

Set rng1 = Sheets("Product KG").ListObjects("Table5").ListColumns(41).DataBodyRange

Sheets("Product KG").Activate
Range("AV7").Select
Selection.FormulaArray = _
"=LARGE((ROUND(" & rng1 & ",2)+ROW(" & rng1 & ")/10000),RC[-5])"

[paulked]

Not tested but try (change Column41 to the header of the column):

Range("AV7").FormulaArray = _"=LARGE((ROUND(Table5[Column41],2)+ROW(Table5[Column41])/10000),RC[-5])"

[Judi]

Yes it works, thank you!

[paulked]

You're welcome , you can mark it Solved (Thread Tools, top right).