• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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
0
Eric Zwiekhorst
Asked:
Eric Zwiekhorst
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
Change both C[-2] bits to C1
0
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
Hi Rorya
I tried this but after changing the cell will no longer accept the formule and stays empty?

kr

Eric
0
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Rory ArchibaldCommented:
What exactly did you change it to?
0
 
Rory ArchibaldCommented:
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
 
Eric ZwiekhorstSAP Business ConsultantAuthor Commented:
Thanks Rorya
that was what  needed.
Would be great if all my question would be answered so swift..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now