Using VBA to insert a formula (getting error)

[Golaidron]

Hi guys

Can you help me with this.

Ark1.Cells(emptyrow, 11).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""B31"")=""Transport1"";""n/a"";IFERROR(IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""H33"")<>""""; _
IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""H33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b33""),"" "",INDIRECT(""'""&[@Fanenavn]&""'!""&""H33""),"" kr"")), _
IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""G33"")<>"""",IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""G33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b33""),"""",INDIRECT(""'""&[@Fanenavn]&""'!""&""G33""),"" kr"")), _
IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""F33"")<>"""",IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""F33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b33""),"""",INDIRECT(""'""&[@Fanenavn]&""'!""&""F33""),"" kr"")), _
IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""E33"")<>"""",IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""E33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b33""),"""",INDIRECT(""'""&[@Fanenavn]&""'!""&""E33""),"" kr"")), _
IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""E33"")="""",IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""D33"")=""n/a",""n/a"",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b33""),"""",INDIRECT(""'""&[@Fanenavn]&""'!""&""D33""),"" kr"")))))));""""));"""")"

The If that is marked out is getting a Compile error: Expected end of statement.
I got 39 ( and 39 )

This formula works.
Ark1.Cells(emptyrow, 12).Select
ActiveCell.FormulaR1C1 = _
"=IFerror(IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""B31"")=""Transport1"",""n/a"",IFERROR(IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""H34"")<>"""",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b34""),"" "",INDIRECT(""'""&[@Fanenavn]&""'!""&""H34""),"" kr""),IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""G34"")<>"""",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b34""),"" "",INDIRECT(""'""&[@Fanenavn]&""'!""&""G34""),"" kr""),IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""F34"")<>"""",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b34""),"" "",INDIRECT(""'""&[@Fanenavn]&""'!""&""F34""),"" kr""),IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""E34"")<>"""",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b34""),"" "",INDIRECT(""'""&[@Fanenavn]&""'!""&""E34""),"" kr""),IF(INDIRECT(""'""&[@Fanenavn]&""'!""&""E34"")="""",CONCATENATE(INDIRECT(""'""&[@Fanenavn]&""'!""&""b34""),"" "",INDIRECT(""'""&[@Fanenavn]&""'!""&""D34"","" kr""))))))),"""")),"""")"


Hope to hear from you soon.

Best regards

Golaidron

 

[Golaidron]

Bumping my post

 

[johnnyL]

Try this:

MyString = """'""&[@Fanenavn]&""'!"""
Ark1.Cells(emptyrow, 11).Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(INDIRECT(MyString&""B31"")=""Transport1"",""n/a"";IFERROR(IF(INDIRECT(MyString&""H33"")<>"""",IF(INDIRECT(MyString&""H33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(MyString&""b33""),"" "",INDIRECT(MyString&""H33""),"" kr"")),IF(INDIRECT(MyString&""G33"")<>"""",IF(INDIRECT(MyString&""G33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(MyString&""b33""),"""",INDIRECT(MyString&""G33""),"" kr"")),IF(INDIRECT(MyString&""F33"")<>"""",IF(INDIRECT(MyString&""F33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(MyString&""b33""),"""",INDIRECT(MyString&""F33""),"" kr"")),IF(INDIRECT(MyString&""E33"")<>"""",IF(INDIRECT(MyString&""E33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(MyString&""b33""),"""",INDIRECT(MyString&""E33""),"" kr"")),IF(INDIRECT(MyString&""E33"")="""",IF(INDIRECT(MyString&""D33"")=""n/a"",""n/a"",CONCATENATE(INDIRECT(MyString&""b33""),"""",INDIRECT(MyString&""D33""),"" kr""))))))),"""")),"""")"