excel - VBA Passing variable to reference Named Ranges - object-defined error -

i'm trying define name ranges , reference them in formula

the worksheet formula =iferror(averageifs('car dashboard.xlsm'!q2_15_video_penetration,'car dashboard.xlsm'!q2_15_module,b5),"")

i passed variables in formula , returning object defined error

sheets("qoq summary").cells(5, 11).formula = "=iferror(averageifs('car dashboard.xlsm'!" & vpenname & ",'car dashboard.xlsm'!" & modulename & ",b5,"")"""

i'm not sure what's wrong it. can please? thanks!

below testing code. please ignore undefined variables.

sub test() dim modulename, vpenname, hpenname, cpenname, xpenname string    'define      modulename = sheets("settings , instruction").range("g1").value    vpenname = sheets("settings , instruction").range("g2").value     activeworkbook.names.add name:=modulename, referstor1c1:= _                 "=offset('" & rp & "'!r5c" & module.column & ",,,counta('" & rp & "'!c" & module.column & "),)"     activeworkbook.names.add name:=vpenname, referstor1c1:= _                 "=offset('" & rp & "'!r5c" & videopen.column & ",,,counta('" & rp & "'!c" & videopen.column & "),)"  dim rng range 'set rng = range("'car dashboard.xlsm'!" & vpenname) 'application.goto rng   sheets("qoq summary").cells(5, 11).formula = _                  "=iferror(averageifs('car dashboard.xlsm'!" & vpenname & ",'car dashboard.xlsm'!" & modulename & ",b5,"")"""  end sub 

your closing bracket in wrong place, try instead:

"=iferror(averageifs('car dashboard.xlsm'!" & vpenname & ",'car dashboard.xlsm'!" & modulename & ",b5,"""")"