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 chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,190
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

ADVERTISEMENT

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

ADVERTISEMENT

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,137,294
Messages
5,680,654
Members
419,923
Latest member
Kalthus

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