# make formula work whith autofill

Hi experts I have following formula that VBA puts in a cell, after that VBA does a autofill
"=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(R[-1]C-DAY(R[-1]C)+1 & "":""&DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,0))))={2,3,4,5,6})*(COUNTIF(Feestdagen!R[-2]C[-2]:R[105]C[-2],ROW(INDIRECT(R[-1]C-DAY(R[-1]C)+1&"":""&DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,0))))=0))"

The Feestdagen!R[-2]C[-2]:R[105]C[-2] should be altered so when it is pasted in FormulaR1C1 the formula shows like Feestdagen!\$A1:\$A108

thanks

Eric
LVL 6
###### Who is Participating?

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.

Commented:
Change both C[-2] bits to C1
0
Hi Rorya
I tried this but after changing the cell will no longer accept the formule and stays empty?

kr

Eric
0
Hi Rorya,

I tried to change it afterwarts and that works but it is not the right approach.
should be Ok from the start

Selection.Replace What:="A1", Replacement:="\$A1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

KR

Eric
0
Commented:
What exactly did you change it to?
0
Commented:
It should have been:

"=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(R[-1]C-DAY(R[-1]C)+1 & "":""&DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,0))))={2,3,4,5,6})*(COUNTIF(Feestdagen!R[-2]C1:R[105]C1,ROW(INDIRECT(R[-1]C-DAY(R[-1]C)+1&"":""&DATE(YEAR(R[-1]C),MONTH(R[-1]C)+1,0))))=0))"
0

Experts Exchange Solution brought to you by