Object Required ? Help Please

[AlaskanVBA]

I'm receiving a 'RunTime Error 424 : Object Required" on the following line:
F = Application.WorksheetFunction.CountIf(R4C9.Resize(FinalRow9, 0), RiC1)
Any advice would be greatly appreciated?

Below is the full code
----

Sub SecondaryC()
Dim WSPC As Worksheet
Dim WSSI As Worksheet

Set WSPC = Worksheets("PrimaryCreation")
Set WSSI = Worksheets("SecondaryInput")
FinalRowC123 = WSSI.Cells(Rows.Count, 1).End(xlUp).Row
FinalRowC9O = WSSI.Cells(Rows.Count, 9).End(xlUp).Row
FinalRowC9F = WSSI.Cells(Rows.Count, 9).End(xlUp).Row
'testing new code

For i = 4 To FinalRowC123
Sec = WSSI.Cells(i, 3)
FinalRowC123 = WSSI.Cells(Rows.Count, 3).End(xlUp).Row

Cells(i, 1).Resize(1, 7).Select
'Find out if From and To inputs are primary or secondary joints'

'Error on next code line?

F = Application.WorksheetFunction.CountIf(R4C9.Resize(5, 0), RiC1)

---------

 

[JoeMo]

Your resize statement is faulty and may be causing the error'

Resize(FinalRow9, 0)

The highlighted argument must be at least 1 (it's the number of columns in the resized range and can't be 0).

 

[AlaskanVBA]

Joe, Appreciate your help but it seems to still not work.
Am I not declaring an object somewhere?

F = Application.WorksheetFunction.CountIf(R4C9.Resize(FinalRowC9O, 1), RiC1)

The above still shows the 'object required' error.

 

[JoeMo]

Try this:

 Dim rng As Range 
Set rng = Range("I4").Resize(FinalRowC90, 1) 
F = Application.WorksheetFunction.CountIf(rng, Cells(i, 1))

 

[AlaskanVBA]

Worked like a charm! Thank you for reminding me to dimension more often - I'll make sure to remember this going forward.



Code:

test