CODEWITHSUNDEEP
vbaexcel
Gautam
Gautam

Reputation: 27

excel-vba Run-time error '1004'

Below is my code

Range("M2").Select
Selection.FormulaArray = _
    "=IF(AND(RC8<=R1C,RC9>=EOMONTH(R1C,0)),INDEX(Planned!R2C:R100C,MATCH(1,(RC3=Planned!R2C3:R100C3)*(RC10=Planned!R2C10:R100C10)*(RC12=Planned!R2C12:R100C12),0)),ROUND((MAX(R1C[1]-MAX(RC8,R1C),0)-MAX(R1C[1]-MAX(RC9,R1C),0))*INDEX(Planned!R2C:R100C,MATCH(1,(RC3=Planned!R2C3:R100C3)*(RC10=Planned!R2C10:R100C10)*(RC12=Planned!R2C12:R100C12),0))/DAY(EOMONTH(R1C,0)),0))"

When I try to run macro it's giving me below error

enter image description here

Any thoughts on this?

Upvotes: 0

Views: 394

Answers (3)

Nikolaos Polygenis
Nikolaos Polygenis

Reputation: 691

please change with this: (The SendKeys method simulates keystrokes that you would manually input in the active window)

Range("M2").Select
Selection.Formula = _
    "=IF(AND(RC8<=R1C,RC9>=EOMONTH(R1C,0)),INDEX(Planned!R2C:R100C,MATCH(1,(RC3=Planned!R2C3:R100C3)*(RC10=Planned!R2C10:R100C10)*(RC12=Planned!R2C12:R100C12),0)),ROUND((MAX(R1C[1]-MAX(RC8,R1C),0)-MAX(R1C[1]-MAX(RC9,R1C),0))*INDEX(Planned!R2C:R100C,MATCH(1,(RC3=Planned!R2C3:R100C3)*(RC10=Planned!R2C10:R100C10)*(RC12=Planned!R2C12:R100C12),0))/DAY(EOMONTH(R1C,0)),0))"


 SendKeys "{F2}"
 SendKeys "^+{ENTER}"

Upvotes: 0

pk_22
pk_22

Reputation: 390

Can you save parts of the formula in variables? And then call the variables in the Formula array? To save on characters? So simply split the equation.

So for example: 

var1 = DAY(EOMONTH(R1C,0))

and then replace it in your formula. Do this for several parts which are easy to replace by a variable.

Upvotes: 1

QHarr
QHarr

Reputation: 84465

Without knowing what data you have in the various fields, does the following work?

ActiveSheet.Range("M2").Activate
With Selection
    Selection.FormulaArray = _
    "=IF(AND(RC8<=R1C,RC9>=EOMONTH(R1C,0)),INDEX(Planned!R2C:R100C,MATCH(1,
   (RC3=Planned!R2C3:R100C3)*(RC10=Planned!R2C10:R100C10)*
   (RC12=Planned!R2C12:R100C12),0)),ROUND((MAX(R1C[1]-MAX(RC8,R1C),0)-
   MAX(R1C[1]-MAX(RC9,R1C),0))*INDEX(Planned!R2C:R100C,MATCH(1,
   (RC3=Planned!R2C3:R100C3)*(RC10=Planned!R2C10:R100C10)*
   (RC12=Planned!R2C12:R100C12),0))/DAY(EOMONTH(R1C,0)),0))"
End With

Upvotes: 1

Related Questions