PDA

View Full Version : [SOLVED] Refer to range in cell formula



Judi
11-22-2019, 12:07 AM
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
11-22-2019, 12:24 AM
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
11-22-2019, 12:42 AM
Yes it works, thank you!

paulked
11-22-2019, 12:45 AM
You're welcome :thumb, you can mark it Solved (Thread Tools, top right).