Using Array w/ R1C1 style?

[AlaskanVBA]

I'm having a hard time defining an array when using R1C1 style.

For example I want to write the following code and I'm getting an error 'obect required'. This is being written after writing a "For i = #" statement. Tried using the resize function below.

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

Any help?

Secondly, if anyone knows of a function which will check to see if the value in Row1Column1 appears in an array from Row1Column2 to Row10Column2 (for example), I'd love to know what function this is.
Does not matter if it returns TRUE/FALSE or a number. Just need a function that 'checks if it shows up elsewhere in a separate array'.

THank you!

 

[pgc01]

Hi

Try:

F = Application.WorksheetFunction.CountIf(Cells(4,9).Resize(FinalRow, 1), Cells(i,1).value)

For the other question, check the help on the Find() method of the Range object.

 

[pgc01]

P. S.

The name of your variable FinalRow leads me to thinking that maybe the statement is not what you want?

The first parameter of Resize() is the number of rows, not the final row.