CODEWITHSUNDEEP
vbafunctionexcel
IIJHFII
IIJHFII

Reputation: 600

Type mismatch when applying defined range to WorksheetFunction

Need to add in an if statement to control what I am looping through in my code so have started with this line:

If Application.WorksheetFunction.Or(checkrange.value = 0, checkrange.value = "") = "False" Then

I have defined checkrange as the following

Set checkrange = finwb.Sheets("Strt_Dash_Final").Range(Cells((cfcll.Row + 1), q), Cells((cfcll.Row + (cfcll.value - 2)), q))

my code stops on the worksheet function line with run-time error 13, Type Mismatch. The purpose of the statment was to check if the range only contains 0s or blanks. The formula OR(A1:A5 = 0,A1:A5 = "") works fine in excel so I am assuming that it is not accepting my range in its current form (Note: i tried removing the .value from checkrange.value but this did not work either)

Any help on this, as always, is greatly appreciated!

EDIT : I have Dim checkrange As range to define it as a range

Upvotes: 0

Views: 148

Answers (4)

Rory
Rory

Reputation: 34045

You need two separate COUNTIF statements:

If Application.WorksheetFunction.Countif(checkrange, "0") + Application.WorksheetFunction.Countif(checkrange, "") = 0 Then

Upvotes: 1

Slai
Slai

Reputation: 22876

The array formula OR(A1:A5 = 0,A1:A5 = "") checks if any (1 or more) of the cells in the range are 0 or "". To check if any of the cells in the range are not 0 or "" then you can use this 

If WorksheetFunction.CountIfs(checkrange, "<>0", checkrange, "<>") > 0 Then

Upvotes: 0

DragonSamu
DragonSamu

Reputation: 1163

There is no need to be using a WorksheetFunction in the If statement.

If checkrange.value = 0 Or checkrange.value = "" Then

Because your checking a Range not a Cell you have to use CountA or CountIf

here is an example of CountA:

If Application.WorksheetFunction.CountA(checkrange) = 0 Then

Upvotes: 1

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

I guess it should be

If Application.WorksheetFunction.Or(checkrange.value = 0, checkrange.value = "") = False Then

though it's not really necessary to use worksheet function OR here. Have a try and let's see what happen.

Upvotes: -2

Related Questions