Error in setting up the VBA Formula : Gettring Error 450 Wrong Number of arguements or invalid property assignment

[NimishK]

Hello

I am getting Error 450 Wrong Number of arguements or invalid property assignment

the following typed in Cell CL3

=SUMIFS('Bills'!S:S,'Bills'!A:A,MISC!A:A,'Bills'!K:K,MISC!$CL$1)-SUMIFS('Bills'!T:T,'Bills'!A:A,MISC!A:A,'Bills'!K:K,MISC!$CL$1)

Tried to incorporate above in VBA Sysntax below and Getting Error 450 Wrong Number of arguements or invalid property assignment

ws.Cells(3, 90).value = Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("S:S"), _
Sheets("Bills").Range("A:A"), Sheets("MISC").Range("A:A)", Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1"))) _
- Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("T:T"), Sheets("Bills").Range("A:A"), _
Sheets("MISC").Range("A:A)", Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1")))

NimishK

 

[Fluff]

You've got quotes in the wrong place

ws.Cells(3, 90).value = Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("S:S"), _
Sheets("Bills").Range("A:A"), Sheets("MISC").Range("A:[COLOR=#ff0000]A)"[/COLOR], Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1"))) _
- Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("T:T"), Sheets("Bills").Range("A:A"), _
Sheets("MISC").Range("A:[COLOR=#ff0000]A)"[/COLOR], Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1")))

 

[Kamolga]

I don't get how your criteria 1 can be Misc!A:A in your formula.

In Application.WorksheetFunction.SumIfs I believe only the two first arguments are ranges. Instead I normally loop trough ranges and add a range value to a variable (TotalSum).

Let's say condition 1 ("John") is for Range("A1:"A100"), condition 2 ("France") is for Range("B1:B100") and we add Range("C1:C100")

​

[LEFT][COLOR=#333333][FONT=monospace]Dim RowNum as long: i=1[/FONT][/COLOR][/LEFT]
Dim TotalSum as long: TotalSum=0

For [COLOR=#333333][FONT=monospace]RowNum[/FONT][/COLOR]=1 to 100
 If (Range("A" & RowNum).value ="John" and Range("B" & RowNum").value = "France") then
    TotalSum=TotalSum+Range("C" & RowNum)
 End if
Next RowNum

[LEFT][COLOR=#333333][FONT=monospace]Range("CL3").value=TotalSum[/FONT][/COLOR][/LEFT]

​

 

[NimishK]

Fluff thanks for the corrections you have shown but after corrections as shown below Type Mismatch

ws.Cells(3, 90).value = Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("S:S"), _
Sheets("Bills").Range("A:A"), Sheets("MISC").Range("A:[B][COLOR=#ff0000]A)"[/COLOR][/B], Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1"))) _
- Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("T:T"), Sheets("Bills").Range("A:A"), _
Sheets("MISC").Range("A:[COLOR=#ff0000][B]A)"[/B][/COLOR], Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1")))

Have corrected But now Type Mismatch Error

ws.Cells(3, 90).value = Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("S:S"), _
Sheets("Bills").Range("A:A"), Sheets("MISC").Range("A:[B][COLOR=#FF0000]A")[/COLOR][/B], Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1"))) _
- Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("T:T"), Sheets("Bills").Range("A:A"), _
Sheets("MISC").Range("A:[COLOR=#FF0000][B]A")[/B][/COLOR], Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1")))

 

[NimishK]

ws.Cells(3, 90).value = Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("S:S"), _
Sheets("Bills").Range("A:A"), Sheets("MISC").Range("A:A"), Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1"))) _
- Application.WorksheetFunction.SumIfs(Sheets("Bills").Range("T:T"), Sheets("Bills").Range("A:A"), _
Sheets("MISC").Range("A:A"), Sheets("Bills").Range("K:K"), Sheets("MISC").Range("$CL$1")))

After the corrections made above Why should i get the Type Mismatch Error
it is the same thing adopted from sheet MISC and in Cell CL93 typed the below

Cell CL93
=SUMIFS('Bills'!S:S,'Bills'!A:A,MISC!A:A,'Bills'!K:K,MISC!$CL$1)-SUMIFS('Bills'!T:T,'Bills'!A:A,MISC!A:A,'Bills'!K:K,MISC!$CL$1)

Do in need to use
ws.Cells(3, 90).Formula instead of ws.Cells(3, 90).value
Have i passed wrong number of arguments in syntax or wrong numberof parenthesis
or what is it ?
Everything tried but in vain

 

[Kamolga]

I would not not use worsheetFunction, there is no point if you want to put the value and when you start typing it from zero, the two first arguments are ranges, the rest are double.

If what you want is to put the formula, you can use

Range("CL93").FormulaR1C1 = "=SUMIFS(Bills!C[-71],Bills!C[-89],MISC!C[-89],Bills!C[-79],MISC!R1C90)-SUMIFS(Bills!C[-70],Bills!C[-89],MISC!C[-89],Bills!C[-79],MISC!R1C90)"

Copy-pasting value is then possible if you don't want to loop through ranges

 

[NimishK]

Kamolga

I just want replica of the formula used in a cell with VBA syntax. If not using WorksheetFunction then what other method do we have

I've not tried as yet your suggestion

Range("CL93").FormulaR1C1 = "=SUMIFS(Bills!C[-71],Bills!C[-89],MISC!C[-89],Bills!C[-79],MISC!R1C90)-SUMIFS(Bills!C[-70],Bills!C[-89],MISC!C[-89],Bills!C[-79],MISC!R1C90)"

Because i dont know if this will take in different rows

Also i did not get you on

the two first arguments are ranges, the rest are double.

 

[NimishK]

https://www.mrexcel.com/forum/excel-questions/1084793-got-stuck-writing-syntax-if-formula.html


below Sir Mikerickson reply #2 on above mentioned thread

Recording a macro of me entering the formula into a cell is the way that I get proper syntax for that formula.

This is what i followed

 

[steve the fish]

Isnt it:

sh.Cells(3, 90).Value = WorksheetFunction.SumIfs(Sheets("Bills").Range("S:S"), Sheets("Bills").Range("A:A"), Sheets("MISC").Range("CL1")) - WorksheetFunction.SumIfs(Sheets("Bills").Range("T:T"), Sheets("Bills").Range("A:A"), Sheets("MISC").Range("CL1"))

 

[NimishK]

Steve the Fish Thanks for clean syntax but i had to change and

With following i resolved

With ws
.Range("CL" & 3).Formula = "=SUMIFS('Bills'!S:S,'Bills'!A:A,MISC!A:A,'Bills'!K:K,MISC!$CL$1)-SUMIFS('Bills'!T:T,Bills'!A:A,MISC!A:A,'Bills'!K:K,MISC!$CL$1)"  
End With

Have changed from ws.cells to ws.Range which made life easier to get the exact Formula above mentioned
Kamolga thanks for your inputs and suggestion surely will implement your suggestions as when need arises as per your post #3

Thanks you so much guys for your valuable time
NimishK