Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA Run-Time Error Can't pin it down

Posted on 2014-11-07
4
Medium Priority
?
136 Views
Last Modified: 2014-11-16
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

0
Comment
Question by:R10131551
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40429289
I don't understand the relationship between the first set of code and the second.

Need more context.
0
 

Author Comment

by:R10131551
ID: 40429303
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40432245
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
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 2000 total points
ID: 40439210
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question