CountIf in VBA

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi I've got the following in my code to check to see whether a reference exists in a sheet but it's not working. Has anyone got a clue why not? :confused:

If WorksheetFunction.CountIf(Sheets("SUMMARY").Range("H6:H65536"), SetRef) > 0 Then GoTo RefExists Else

SetRef is a string relating to a cell on another sheet and RefExists is a message box telling the user the reference is already included on the summary sheet.

Cheers,
rrenis :biggrin:
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,813
Office Version
  1. 365
Platform
  1. Windows
Don't know if it applies here, but I've always used "Application.WorksheetFunction", rather than just "WorksheetFunction."
Wouldn't be that I suppose would it?........
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Thanks for the tip sykes - I just tried it with Application.WorksheetFunction and it still doesn't work :(
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,813
Office Version
  1. 365
Platform
  1. Windows
OK, when you say it isn't working, exactly what's happening (or not) are you getting an error, or is the code not going to RefExists when you expect it to?
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,813
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

......... the "else" statement probably needs to be on the next line as well, rather than at the end of the line in question.........
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi sykes - it's not going to RefExists, instead it runs the rest of the code which inserts the details for a particular reference :confused:

I was just after something that would stop multiple entries of the same reference and thought this countif method would be straight forward!! :eek:

Cheers,
rrenis
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120

ADVERTISEMENT

thanks for the advice on the else placement. I'll move it to the next line.

Cheers,
rrenis
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,813
Office Version
  1. 365
Platform
  1. Windows
I know what you mean - you think something's going to be a sinch, and it turns into a marathon - normally because of something silly, miles away you didn't think of!!

..... if moving the "else" didn't work, perhaps we need to ensure a few things are as you think they are:

1. You could use
Code:
msgbox setRef
...immediately before the line in question, to test that SetRef is populated with what you think it should be.
2. Check that the string in SetRef is definitely within
Sheets("SUMMARY").Range("H6:H65536")
(exactly as in SetRef) at least once (otherwise, of course, it won't drive the code to RefExists)
3. Check that RefExists has a colon after it.

............all teaching Granny to suck eggs, I know, but these are some of the mistakes I find myself suffering from...!!
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Thanks sykes - useful tests there which will hopefully highlight the problem! I'll have to try them on monday though as I'm just about to leave the office.

Thanks for your time! :biggrin:

Cheers,
rrenis
 

rrenis

Board Regular
Joined
Oct 22, 2004
Messages
120
Hi sykes! Thanks for your help last week. You hit the nail on the head with..

you think something's going to be a sinch, and it turns into a marathon - normally because of something silly

In my rush to leave the office I closed excel down without saving (Doh!) - so had to re-enter the code today. And guess what - it works fine!! :oops:

Thanks again for sparing your time to help. If I had a dunce cap handy it would be firmly on my head! :biggrin:

cheers,
rrenis :)
 

Forum statistics

Threads
1,136,430
Messages
5,675,803
Members
419,586
Latest member
RoteichA

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