Type Mismatch Error, Please Help?

[antm79]

Hello All,

I Keep getting a type mismatch error when trying to execute the following code. I want to search for a column heading 'goods in' then return the reoccurrence of the partial text string 'Supplies' but I keep getting a 'Type Mismatch Error' on the code line that contains the count if function. Is anyone able to help?

Sub Count()
Dim Strrng As Range
Dim StrCol As Long
Dim StrResult As Long


Set Strrng = Rows(2).Find("Goods In")
StrCol = Strrng.Column
StrResult = Application.CountIf(StrCol, "*" & "Supplies" & "*")
 
End Sub

Thanks in advance,
Antm79

 

[RoryA]

CountIf requires a range as its first argument:

Dim Strrng As Range
Dim rgCol As Range
Dim StrResult As Long


Set Strrng = Rows(2).Find("Goods In")
Set rgCol = Strrng.EntireColumn
StrResult = Application.CountIf(rgCol, "*" & "Supplies" & "*")

 

[Jonmo1]

The first argument of the Countif needs to be a range
You have it as just a number.

Say the string Goods In was found in H2 for example
then StrCol = Strrng.Column returns 8 (H is the 8th column)
Then your countif is translated to
StrResult = Application.CountIf(8, "*" & "Supplies" & "*")

Try dimming StrCol as RANGE instead of Long
And
Set StrCol = Strrng.EntireColumn

 

[antm79]

Great, thanks for your help Rory.

 

[antm79]

Jonmo1,

Thanks for the Clarification.

antm79

 

[antm79]

Hi Guys,

I made the changes As suggested but I now get an error Saying 'Object variable or with block variable not set' Any Ideas?

Sub Count()


Dim Strrng As Range
Dim StrCol As Range
Dim StrResult As Long


Set Strrng = Rows(2).Find("Goods In")
StrCol = Strrng.EntireColumn
StrResult = Application.CountIf(StrCol, "*" & "Supplies" & "*")
 
End Sub
[\CODE]

Thanks,
Antm79

 

[Jonmo1]

Set StrCol = Strrng.EntireColumn

 

[antm79]

Hi Jonmo,

Thanks, I have just done that but still get the sam error?

Sub Count()


Dim Strrng As Range
Dim StrCol As Range
Dim StrResult As Long


Set Strrng = Rows(2).Find("Goods In")
Set StrCol = Strrng.EntireColumn
StrResult = Application.CountIf(StrCol, "*" & "Supplies" & "*")
 
End Sub
[\CODE]

 

[Jonmo1]

Which line is highlighted when you click Debug?

Sounds like the string "Goods In" was NOT found in Row2 of the Active Sheet.

 

[antm79]

It was the string,Sorted now, thanks very much for your help and patience.

antm79