0

First off apologies for the unsightly formulae below - I'm not sure how I can make them easier to read without potentially removing required info for a solution.

I'm trying to automate some process in Excel, and am getting

run time error 1004

each time I try to step through the vba to populate the active cell with either of the following formulae. The formulae have been tested in Excel (I then recorded the entry and put into the vba).

1)

ActiveCell.FormulaR1C1 = _
    "=16*POWER(SQRT(((SUMIFS(C[-5],C[-2],RC[-2],C[-1],RC[-1])-RC[-5])/(SUMIFS(C[-6],C[-2],RC[-2],C[-1],RC[-1])-RC[-6]))*(1-((SUMIFS(C[-5],C[-2],RC[-2],C[-1],RC[-1])-RC[-5])/(SUMIFS(C[-6],C[-2],RC[-2],C[-1],RC[-1])-RC[-6]))))/(((SUMIFS(C[-5],C[-2],RC[-2],C[-1],RC[-1])-RC[-5])/(SUMIFS(C[-6],C[-2],RC[-2],C[-1],RC[-1])-RC[-6]))*((RC[-5]/RC[-6])-((SUMIFS(C[-5],C[-2],RC[-2],C[" & _
    "1])-RC[-5])/(SUMIFS(C[-6],C[-2],RC[-2],C[-1],RC[-1])-RC[-6])))/((SUMIFS(C[-5],C[-2],RC[-2],C[-1],RC[-1])-RC[-5])/(SUMIFS(C[-6],C[-2],RC[-2],C[-1],RC[-1])-RC[-6]))),2)"

2)

ActiveCell.FormulaR1C1 = _
    "=IF(AND(IFERROR(IF(OR(NORMDIST((((SUMIFS(C[-7],C21,RC21,C22,RC22)-RC[-7])/(SUMIFS(C[-8],C21,RC21,C22,RC22)-RC[-8]))-(RC[-7]/RC[-8]))/SQRT(POWER(SQRT((((SUMIFS(C[-7],C21,RC21,C22,RC22)-RC[-7])/(SUMIFS(C[-8],C21,RC21,C22,RC22)-RC[-8]))*(1-((SUMIFS(C[-7],C21,RC21,C22,RC22)-RC[-7])/(SUMIFS(C[-8],C21,RC21,C22,RC22)-RC[-8])))/(SUMIFS(C[-8],C21,RC21,C22,RC22)-RC[-8]))),2)+" & _
    "RT(((RC[-7]/RC[-8])*(1-(RC[-7]/RC[-8]))/RC[-8])),2)),0,1,TRUE)<(1-cpanel!R4C3),NORMDIST((((SUMIFS(C[-7],C21,RC21,C22,RC22)-RC[-7])/(SUMIFS(C[-8],C21,RC21,C22,RC22)-RC[-8]))-(RC[-7]/RC[-8]))/SQRT(POWER(SQRT((((SUMIFS(C[-7],C21,RC21,C22,RC22)-RC[-7])/(SUMIFS(C[-8],C21,RC21,C22,RC[-3])-RC[-8]))*(1-((SUMIFS(C[-7],C21,RC21,C22,RC[-3])-RC[-7])/(SUMIFS(C[-8],C21,RC21,C22,R" & _
    "8])))/(SUMIFS(C[-8],C21,RC21,C22,RC22)-RC[-8]))),2)+POWER(SQRT(((RC[-7]/RC[-8])*(1-(RC[-7]/RC[-8]))/RC[-8])),2)),0,1,TRUE)>cpanel!R4C3), ""YES"", ""NO""),""NO"")=""YES"",RC[-8]>RC[-2]),IF(RC[-1]=TRUE,""WINNER"",""LOSER""),""NOTSIG"")"

Is there any basic syntax that I'm misunderstanding here?

Thanks in advance for your time.

Karthick Kumar
  • 2,349
  • 1
  • 17
  • 30
seymourgoestohollywood
  • 1,157
  • 10
  • 18
  • 1
    What problem are you trying to solve? – Doug Glancy Apr 17 '14 at 12:43
  • 1
    For the purpose of debugging, place a single quote (apostrophe) in front of the equal sign in the deposited formula. This will cause your macro to place the formula as text. This should enable you to see any problems by inspecting the cell contents. – Gary's Student Apr 17 '14 at 12:45
  • Its a single cell calc as part of a wider minimum sample size calculation. I've inspected as text but relative column referencing makes it a bit more difficult. The formula works fine in Excel when manually inputted, but when i record inputting the formula (where it is implemented correctly) and then replay the entry in vba, the error occurs, making me think its vba syntax related? Could be wrong though. – seymourgoestohollywood Apr 17 '14 at 13:03
  • I've now solved this - the issue related to surpassing the 255 character limit, and breaking the formula up with .Replace works. Thanks all! – seymourgoestohollywood Apr 17 '14 at 15:27

0 Answers0