Countif formula to vba giving Run time error 1004

Question

I am trying to convert Countif formulas into a vba formats but its giving me a run time error 1004 application or object define error...

My code and formulas:

For example,

=COUNTIF('Sheet1'!$D$14:$D$3000,$B25)-COUNTIFS('Sheet1'!$D$14:$D$3000,B25,'Sheet1'!$E$14:$E$3000,"No Test")

to

Range("A1").Formula = "=COUNTIF('Sheet1'!" & Sheets("Sheet1").Range(Cells(14, 4), Cells(3000, 4)).Address(False, False) & "," & _ 
Sheets("Sheet1").Cells(25, 2).Address(False, False) & ") -" & _
"Countifs('Sheet1'!" & Sheets("Sheet1").Range(Cells(14, 4), Cells(3000, 4)).Address(False, False) & "," & _ 
Sheets("Sheet1").Cells(25, 2).Address(False, False) & ",'Sheet1!" & _
Sheets("Sheet1").Range(Cells(14, 5), Cells(3000, 5)).Address(False, False) & ", ""No Test"")"

Answer 1

The formula itself aside - the reason for the error you are reporting is because of a missing apostrophe when decaring the sheet name:

The below fills in the formula as I imagine it is required:

Range("A1").Formula = "=COUNTIF('Sheet1'!" & Sheets("Sheet1").Range(Cells(14, 4), Cells(3000, 4)).Address(False, False) & "," & _
Sheets("Sheet1").Cells(25, 2).Address(False, False) & ") -" & _
"Countifs('Sheet1'!" & Sheets("Sheet1").Range(Cells(14, 4), Cells(3000, 4)).Address(False, False) & "," & _
Sheets("Sheet1").Cells(25, 2).Address(False, False) & ",'Sheet1'!" & _
Sheets("Sheet1").Range(Cells(14, 5), Cells(3000, 5)).Address(False, False) & ", ""No Test"")"

Answer 2

You haven't properly qualified all the .Cells calls with a worksheet object, but since you only want the addresses, you don't actually need to specify a worksheet:

Range("A1").Formula = "=COUNTIF('Sheet1'!" & Range(Cells(14, 4), Cells(3000, 4)).Address(False, False) & "," & _ 
Cells(25, 2).Address(False, False) & ") -" & _
"Countifs('Sheet1'!" & Range(Cells(14, 4), Cells(3000, 4)).Address(False, False) & "," & _ 
Cells(25, 2).Address(False, False) & ",'Sheet1'!" & _
Range(Cells(14, 5), Cells(3000, 5)).Address(False, False) & ", ""No Test"")"

Answer 3

You are specifying the parent of Range but not of Cells. The Cells are defaulting to another worksheet's cells. Essentially you are saying give me a range on Sheet1 that contains the cells in Sheet2.

Use a With/End With and prefix .Range and .Cells with a period to show parentage.

Dim ws2 As Worksheet

Set ws2 = Sheets("Sheet2")

With Sheets("Sheet1")
    ws2.Range("A1").Formula = "=COUNTIF(" & .Range(.Cells(14, 4), .Cells(3000, 4)).Address(0, 0, external:=True) & "," & .Cells(25, 2).Address(0, 0) & ")-" & _
       "COUNTIFS(" & .Range(.Cells(14, 4), .Cells(3000, 4)).Address(0, 0, external:=True) & "," & .Cells(25, 2).Address(0, 0) & ", " & _
       .Range(.Cells(14, 5), .Cells(3000, 5)).Address(0, 0, external:=True) & ", ""No Test"")"
End With

Note that I've used external:=True to include the worksheet names. There does appear to be some confusion as to which worksheet B25 belongs to.

This is what the formula resolves to:

=COUNTIF(Sheet1!D14:D3000, B25)-COUNTIFS(Sheet1!D14:D3000, B25, Sheet1!E14:E3000, "No Test")