Type Mismatch Error, Please Help?

antm79

Board Regular
Joined
Nov 29, 2013
Messages
60
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?

Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
39,057
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
CountIf requires a range as its first argument:
Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Nov 29, 2013
Messages
60
Jonmo1,

Thanks for the Clarification.

antm79
 

antm79

Board Regular
Joined
Nov 29, 2013
Messages
60
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?

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Set StrCol = Strrng.EntireColumn
 

antm79

Board Regular
Joined
Nov 29, 2013
Messages
60
Hi Jonmo,

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

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Which line is highlighted when you click Debug?

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

antm79

Board Regular
Joined
Nov 29, 2013
Messages
60
It was the string,Sorted now, thanks very much for your help and patience.

antm79
 

Forum statistics

Threads
1,171,685
Messages
5,876,889
Members
433,217
Latest member
Muhammad Tanzeel Ur Rehma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top