How to use countifs formula in vba macro if there are more than 255 characters

[Sakshi]

Hi

I have used countifs formula with indirect and vlookup in excel cell and it is working prefectly fine...


But it made my excel too heavy the size is approx 33 mb as there are multiple dropdowns on which basis that formula is running...

I thought to use that in vba code but it is giving error subscript out of range...
i tried to split the formula by creating different variables bit its not working


Can anyone suggest me any vba code of splitting countifs formula if there more than 255 characters.

please note all calculation of countifs should be in a single cell....please advise

 

[NARAYANK991]

Hi ,

Please post the worksheet formula , which is working in a worksheet cell , and which you now want to implement using VBA.

Narayan

 

[Sakshi]

Hi Narayan,

thanks for your reply..

please find the formula below which is working fine in excel worksheet, however, not working in vba code...

=COUNTIFS(INDIRECT(VLOOKUP($B$6,'Reference Table'!$B$4:$E$10,2,0)),IF('sheet1'!$C$6="all","*",'sheet1'!$C$6),INDIRECT(VLOOKUP('sheet1'!$B$6,'Reference Table'!$B$4:$E$10,3,0)),IF('sheet1'!$D$6="all","*",'sheet1'!$D$6),INDIRECT(VLOOKUP($B$6,'Reference Table'!$B$4:$E$10,4,0)),IF('sheet1'!$E$6="all","*",'sheet1'!$E$6),'Main Data'!$A:$A,IF('sheet1'!$H$6="Global","*",'sheet1'!$H$6),'Main Data'!$M:$M,IF('sheet1'!$H$7="all","*",'sheet1'!$H$7))


in code i have used this as

sheets("Summary").Range ("C13").Formula = "
=COUNTIFS(INDIRECT(VLOOKUP($B$6,'Reference Table'!$B$4:$E$10,2,0)),IF('sheet1'!$C$6=""all"",""*"",'sheet1'!$C$6),INDIRECT(VLOOKUP('sheet1'!$B$6,'Reference Table'!$B$4:$E$10,3,0)),IF('sheet1'!$D$6=""all"",""*"",'sheet1'!$D$6),INDIRECT(VLOOKUP($B$6,'Reference Table'!$B$4:$E$10,4,0)),IF('sheet1'!$E$6=""all"",""*"",'sheet1'!$E$6),'Main Data'!$A:$A,IF('sheet1'!$H$6=""Global"",""*"",'sheet1'!$H$6),'Main Data'!$M:$M,IF('sheet1'!$H$7=""all"",""*"",'sheet1'!$H$7))"

the error i am getting is Subscript out of range...I tried to split the code with different variables but its not working..

Could you please suggest any code so that above formula should work fine..

I have different cell range for e.g. C13 I have above formula and for C14, C15 i have another countifs formula with same format....and strings/array or criteria range will be increased..

therefore I am looking for dynamic code which will work fine no matter how many arrays/strings or criterias in the countifs.....please advise....