recording formula help

[jordanburch]

Hey guys so i recorded the follwing

=ROUND(SUMPRODUCT(($AR163:$AR3161=AR944)+0,SUBTOTAL(109,OFFSET($J163:$J3161,ROW($J163:$J3161)-MIN(ROW($J163:$J3161)),0,1,1))),2)

it came out as

dng.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=ROUND(SUMPRODUCT((R[-781]C44:R[2217]C44=RC[-1])+0,SUBTOTAL(109,OFFSET(R[-781]C10:R[2217]C10,ROW(R[-781]C10:R[2217]C10)-MIN(ROW(R[-781]C10:R[2217]C10)),0,1,1))),2)"

it works for the bottom mos visible cells but there are cell in between and thats why its failing. any sugesstions?

Jordan

 

[jasonb75]

thats why its failing.

How do you define failing? Are you getting an error message or is it simply not doing what you want it to?

 

[jordanburch]

How do you define failing? Are you getting an error message or is it simply not doing what you want it to?

its basically not inserting the formulas correctly. I believe its because some of the filtered cells are filtered and it stops at the first filter and then goes to the next filter. I need it to give me $J2:$J3500 not the J reference it is returning.

 

[jasonb75]

If you set $J2:$J3500 without adjusting the rest of it proportionately then it will result in an error. There are a number of things about your formula that look wrong but without an example of what you're trying to do it is impossible to say for certain. See if this does what you need.

dng.SpecialCells(xlCellTypeVisible).Formula = "=ROUND(SUMPRODUCT(($AR$2:$AR$3500=AR2)*SUBTOTAL(109,OFFSET($J$2,ROW($J$2:$J$3500)-ROW($J$2),0,1,1))),2)"