Rudimentary VBA Question -- Formula calculate in VBA display result

[ItalianPlatinum]

Hello - I have a very basic VBA question. I have two cells; where I want VBA to calculate the result in VBA and then just put the value in the cell for example:

Current
Cell B5: =COUNTIFS('D'!B1:B1000,$A$3)
Cell B6: =COUNTIFS(C1:C1000,">"&0,C1:C1000,"<>98",F1:F1000,"M")

Desired

VBA handles the calculation

Cell B5: result only
Cell B6: result only

 

[Fluff]

How about

Range("B5").Value = [COUNTIFS('D'!B1:B1000,$A$3)]
Range("B6").Value = [COUNTIFS(C1:C1000,">"&0,C1:C1000,"<>98",F1:F1000,"M")]

 

[ItalianPlatinum]

How about

[QUOTE="Fluff, post: 5608954, member: 289073"]
Range("B5").Value = [COUNTIFS('D'!B1:B1000,$A$3)]
Range("B6").Value = [COUNTIFS(C1:C1000,">"&0,C1:C1000,"<>98",F1:F1000,"M")]

[/QUOTE]
I am getting this error is it related to me added this?

#VALUE!

With Sheets("Compare")
.Range("B5").Value = [COUNTIFS('D'!B1:B1000,$A$3))]
.Range("B6").Value = [COUNTIFS(C1:C1000,">"&0,C1:C1000,"<>98",F1:F1000,"M"))]
End With

 

[joeu2004]

Desired
VBA handles the calculation

Range("B5") = WorksheetFunction.CountIfs(Sheets("D").Range("B1:B1000"), Range("A3"))
Range("B6") = WorksheetFunction.CountIfs(Range("C1:C1000", ">0", Range("C1:C1000", "<>98", Range("F1:F1000", "M"))

 

[Fluff]

I am getting this error is it related to me added this?

Yes, remove the extra closing bracket ")" from each formula.

 

[Fluff]

You also need to add a period before the square brackets like

With Sheets("Compare")
   .Range("B5").Value = .[COUNTIFS('D'!B1:B1000,$A$3)]
   .Range("B6").Value = .[COUNTIFS(C1:C1000,">"&0,C1:C1000,"<>98",F1:F1000,"M")]
End With

 

[ItalianPlatinum]

Yes, remove the extra closing bracket ")" from each formula.

WORKS. I forgot the period at first

 

[Fluff]

Is that with the amendment in post#6?