VBA Run-Time Error Can't pin it down

Hello, I just joined today!
I'm using VBA in Excel 2010. My code takes a cell value does several things too it and adds it to a table in another sheet. After this it compares the column header of the changed cell to an VBA IF statement and selects the correct formula to insert into the changed cell. The selection of the formula is working correctly however after the formula is selected it is passed back and it attempts to insert into the changed cell I then run into my run time error.
I'm not sure what is causing the issue.
Any ideas as to a solution?

This is where the run-time error occurs
cell is defined as a range and formula_1 is defined as a string
'Insert formula_1 into the changed cell
                cell.Formula = formula_1

Open in new window

'
Formula selection code
Public Function WorksheetFormula(ByVal target As Range, ColumnHeader As String, formula_1 As String, columnFormula As String)

If ColumnHeader = "% Discount" Then
           formula_1 = "=IFERROR((GETPIVOTDATA(""<wbr ></wbr>Max of ""&INDIRECT((ADDRESS(14,CO<wbr ></wbr>LUMN()))),<wbr ></wbr>'Database1<wbr ></wbr> PivotTable'!$A$1," & _
           """KEY"",LEFT(INDIRECT(CON<wbr ></wbr>CATENATE("<wbr ></wbr>"B"",SUM(R<wbr ></wbr>OW()-1))),<wbr ></wbr>4)&Current<wbr ></wbr>HFMFamily&<wbr ></wbr>(OFFSET(IN<wbr ></wbr>DIRECT(ADD<wbr ></wbr>RESS(ROW()<wbr ></wbr>,COLUMN())<wbr ></wbr>),-(SUM(RO<wbr ></wbr>W()-(6))),<wbr ></wbr>-(MOD(COLU<wbr ></wbr>MN()+1,4))<wbr ></wbr>))" & _
           "&CurrentYear,""PRODUCT"",<wbr ></wbr>CurrentHFM<wbr ></wbr>Family,""P<wbr ></wbr>ROGRAM"",L<wbr ></wbr>EFT(INDIRE<wbr ></wbr>CT(CONCATE<wbr ></wbr>NATE(""B""<wbr ></wbr>,SUM(ROW()<wbr ></wbr>-1))),4),"<wbr ></wbr> & _
           """CUSTOMERSEG"",CurrentCu<wbr ></wbr>stomerSegm<wbr ></wbr>ent,""MONT<wbr ></wbr>H"",(OFFSE<wbr ></wbr>T(INDIRECT<wbr ></wbr>(ADDRESS(R<wbr ></wbr>OW(),COLUM<wbr ></wbr>N())),-(SU<wbr ></wbr>M(ROW()-(6<wbr ></wbr>))),-(MOD(<wbr ></wbr>COLUMN()+1<wbr ></wbr>,4)))),""Y<wbr ></wbr>EAR"",Curr<wbr ></wbr>entYear)),<wbr ></wbr>"")"
        
        ElseIf ColumnHeader = "% Take" Then
            formula_1 = "=IFERROR((GETPIVOTDATA(""<wbr ></wbr>Max of ""&INDIRECT((ADDRESS(14,CO<wbr ></wbr>LUMN()))),<wbr ></wbr>'Database1<wbr ></wbr> PivotTable'!$A$1," & _
            """KEY"",LEFT(INDIRECT(CON<wbr ></wbr>CATENATE("<wbr ></wbr>"B"",SUM(R<wbr ></wbr>OW()-1))),<wbr ></wbr>4)&Current<wbr ></wbr>HFMFamily&<wbr ></wbr>(OFFSET(IN<wbr ></wbr>DIRECT(ADD<wbr ></wbr>RESS(ROW()<wbr ></wbr>,COLUMN())<wbr ></wbr>),-(SUM(RO<wbr ></wbr>W()-(6))),<wbr ></wbr>-(MOD(COLU<wbr ></wbr>MN()+1,4))<wbr ></wbr>))" & _
            "&CurrentYear,""PRODUCT"",<wbr ></wbr>CurrentHFM<wbr ></wbr>Family,""P<wbr ></wbr>ROGRAM"",L<wbr ></wbr>EFT(INDIRE<wbr ></wbr>CT(CONCATE<wbr ></wbr>NATE(""B""<wbr ></wbr>,SUM(ROW()<wbr ></wbr>-1))),4),"<wbr ></wbr> & _
            """CUSTOMERSEG"",CurrentCu<wbr ></wbr>stomerSegm<wbr ></wbr>ent,""MONT<wbr ></wbr>H"",(OFFSE<wbr ></wbr>T(INDIRECT<wbr ></wbr>(ADDRESS(R<wbr ></wbr>OW(),COLUM<wbr ></wbr>N())),-(SU<wbr ></wbr>M(ROW()-(6<wbr ></wbr>))),-(MOD(<wbr ></wbr>COLUMN()+1<wbr ></wbr>,4)))),""Y<wbr ></wbr>EAR"",Curr<wbr ></wbr>entYear)),<wbr ></wbr>"")"
        
        ElseIf ColumnHeader = "Dollars" Then
            formula_1 = "=PRODUCT((OFFSET(INDIRECT<wbr ></wbr>(ADDRESS(R<wbr ></wbr>OW(),COLUM<wbr ></wbr>N())),-(SU<wbr ></wbr>M(ROW()-(8<wbr ></wbr>))),(MOD(C<wbr ></wbr>OLUMN()+1,<wbr ></wbr>4)))),(OFF<wbr ></wbr>SET(INDIRE<wbr ></wbr>CT(ADDRESS<wbr ></wbr>(ROW(),COL<wbr ></wbr>UMN())),0,<wbr ></wbr>1))," & _
            "(OFFSET(INDIRECT(ADDRESS(<wbr ></wbr>ROW(),COLU<wbr ></wbr>MN())),0,2<wbr ></wbr>)))"
End If
   columnFormula = formula_1
End Function

Open in new window

R10131551Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I don't understand the relationship between the first set of code and the second.

Need more context.
0
R10131551Author Commented:
The second set is the formula selection once this is determined it is set to variable "formula_1". This value is passed to the first set on line "cell.formula = formula_1". On this line I'm getting the 1004 error.

The problem seems to lie with using the "formula_1" string variable to house the formula.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can you show me the context for calling "WorksheetFormula".

My best guess at this stage - you have not declared formula_1 with sufficient scope (e.g. a Dim or Public statement or similar).
0
Michael FowlerSolutions ConsultantCommented:
Place a message box in your code check formula_1 is defined at runtime

MsgBox formula_1
cell.Formula = formula_1

Open in new window


If it is not set then try declaring formula_1 as a global variable. That is declare it outside of a Function/Sub. Have a look at this link for more details
http://www.functionx.com/vbaexcel/Lesson03.htm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.