CountIf in VBA

[rrenis]

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?

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

 

[sykes]

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]

Thanks for the tip sykes - I just tried it with Application.WorksheetFunction and it still doesn't work

 

[sykes]

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]

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

 

[rrenis]

Hi sykes - it's not going to RefExists, instead it runs the rest of the code which inserts the details for a particular reference

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

Cheers,
rrenis

 

[rrenis]

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

Cheers,
rrenis

 

[sykes]

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

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]

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!

Cheers,
rrenis

 

[rrenis]

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!!

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

cheers,
rrenis