excel - VBA formula too long, ways to shorten it -

goodafternoon all,

currently facing vba issue, "record macro" function work, when try rerun it, gives me 1004 error. recall 3 things:

1. split formula in parts, refer next part in formula (ie part 2= abc+def+ghi, formula 123+3456+ ____, replace ____ part 2)
2. put formula text in different cells, refer cells in formula (formula cell("a1").value(?) + cell("b2").value(?)
3. give parts of code specific name, shorten formula (ie abc = 'input - motc!' refer abc in formula

especially part 2 use with, since don't know exact reference, , can @ formula in excel instead of going vba.

the formula is:

activecell.formular1c1 = _     "=if(weekday(rc2)=6,countifs('input - motc'!c90,""confirmed"",'input - motc'!c101,""1"",'input - motc'!c74,""500"",'input - motc'!c86,""<>c"",'input - motc'!c47,""<""&r2c11,'input - motc'!c45,""<""&r2c12,'input - motc'!c93,""no"",'input - motc'!c105,""no"",'input - motc'!c99,""no"",'input - motc'!c47,rc2)" & _     "+countifs('input - motc'!c90,""confirmed"",'input - motc'!c101,""1"",'input - motc'!c74,""500"",'input - motc'!c86,""<>c"",'input - motc'!c47,""<""&r2c11,'input - motc'!c45,""<""&r2c12,'input - motc'!c93,""no"",'input - motc'!c105,""no"",'input - motc'!c99,""no"",'input - motc'!c47,rc2+1)" & _     "+countifs('input - motc'!c90,""confirmed"",'input - motc'!c101,""1"",'input - motc'!c74,""500"",'input - motc'!c86,""<>c"",'input - motc'!c47,""<""&r2c11,'input - motc'!c45,""<""&r2c12,'input - motc'!c93,""no"",'input - motc'!c105,""no"",'input - motc'!c99,""no"",'input - motc'!c47,rc2+2)" & _     ",countifs('input - motc'!c90,""confirmed"",'input - motc'!c101,""1"",'input - motc'!c74,""500"",'input - motc'!c86,""<>c"",'input - motc'!c47,""<""&r2c11,'input - motc'!c45,""<""&r2c12,'input - motc'!c93,""no"",'input - motc'!c105,""no"",'input - motc'!c99,""no"",'input - motc'!c47,rc2)" 

am doing wrong, or formula long?

(little background, add values of saturday&sunday value of friday, if weekday 6, so).

thanks in advance :)

you shorten formula this:

activecell.formular1c1 = _     "=if(weekday(rc2)=6,sum(countifs('input - motc'!c90,""confirmed"",'input - motc'!c101,""1"",'input - motc'!c74,""500"",'input - motc'!c86,""<>c"",'input - motc'!c47,""<""&r2c11,'input - motc'!c45,""<""&r2c12,'input - motc'!c93,""no"",'input - motc'!c105,""no"",'input - motc'!c99,""no"",'input - motc'!c47,rc2+{0,1,2}))" & _     ",countifs('input - motc'!c90,""confirmed"",'input - motc'!c101,""1"",'input - motc'!c74,""500"",'input - motc'!c86,""<>c"",'input - motc'!c47,""<""&r2c11,'input - motc'!c45,""<""&r2c12,'input - motc'!c93,""no"",'input - motc'!c105,""no"",'input - motc'!c99,""no"",'input - motc'!c47,rc2))"