If...Then routine not recognised when it should be 1

[telephoto]

Hi guys

Background:
Every year I receive a schedule of sports matches for the whole division, sorted by date.
(This could be anything eg soccer, but is actually bowls)
First thing I want to do is make a list of my team's matches.
VBA to the rescue.
I examine each line of the spreadsheet for my team's name, automatically, completes in seconds.

Send the routine to the central organiser so anyone can use it.

Everything is fine apart from one club: "City of Ely"

The If...Then routine below is activated when the targetclub variable = the interrogated cell.
Only, when City of Ely is selected it steps past directly to Next If.
Testing, capitalising "of" to "Of" works OK.

Can anyone explain what's happening please?

                If Range(RangeToSelect) = targetclub Then 'select a-c
                        RangeToSelect = "A" & (n + p) & ":C" & (n + p)
                        Range(RangeToSelect).Select
                        Selection.Cut 'paste into celladdress
                        Range(CellAddress).Select
                        ActiveSheet.Paste
                End If

 

[Andrzejek]

Check both sides of your If statement, something like:

[blue]
Debug Print "Range: *" & Range(RangeToSelect) & "* vs targetclub: *" & targetclub  & "*"[/blue]
If Range(RangeToSelect) = targetclub Then
    ...

I put * around the values because sometimes there are spaces that are hard to see.

> capitalising "of" to "Of" works OK
You may want to try:

If [blue]UCase([/blue]Range(RangeToSelect)[blue])[/blue] = [blue]UCase([/blue]targetclub[blue])[/blue] Then
    ...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Hi,

First, this is not a method to test the intersection of two ranges.

Second, you're assuming that this kind of approach is a good approach.

Please post a sample of your worksheet. I assume that your explanation above will suffice, but you may need to explain in more detail what outcome you want to achieve.

BTW, a typical approach for evaluating two or more ranges is...

If Not Intersect(Range1, Range2[COLOR=#888A85],...Range[i]n[/i][/color]) Is Nothing Then
...
End If

…and all this tells you is that there is at least ONE CELL that intersect.

So we need a LOT more info.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[telephoto]

Thank you to both,
Andy
First I tried several debug prints for targetclub throughout the module, all were correct
I checked the if/then line step by step using F8 and hovering the cursor. Both sides matched but it stepped directly to endif.
Using Ucase is a much simpler work-around than I used. Checked and it works and I will use it, but it is not an explanation why "of" fails.

Skip
It's late in the UK, and you are using expressions I have never come across before....
Believe me, I am sure there would be a better way, but I am using VBA as a faster way of doing something which I have done manually, so I am building it slowly using simple code blocks.
Quote from your answer:
"and all this tells you is that there is at least ONE CELL that intersect."
If you mean my code, then that's correct, but I then know which cell is the target club.
I extract the adjacent cells holding "my team" "v" "opponents" and insert them against the date, which is on a line of its own above the schedule for that day.
Once I have checked the list I go down the sheet deleting rows That do not have "my team" in a cell.
This information I use for the handbook and (eventually)a DB table
see jpg for before and after to see the result of the program

Note to self: check out "if not intersect" (but it won't be until later tomorrow)
Skip, thank you, I will check it, but it still doesn't explain why "of" fails.
Thanks again to both
Telephoto

 

[SkipVought]

Your SOURCE data needs conditioning into a single table like this.
I'm only showing your team in two games, at home and away, but you can see that the resulting filtered data will show ALL your games...
[pre]
Date Home Vs Visitor
Friday, May 7, 2021 BottishamB V Milton
Friday, May 7, 2021 City Ely V Littleport B
Monday, May 10, 2021 Soham B V Ely Beet
Monday, May 10, 2021 West Row V City Ely
[/pre]

Then insert 3 rows above the table, use Data > Advanced Filter, and assign the proper ranges for the data and the criteria in the dialog.
BTW you can filter in place or to another location...
[pre]
Home Visitor
City Ely
City Ely
Date Home Vs Visitor
Friday, May 7, 2021 City Ely V Littleport B
Monday, May 10, 2021 West Row V City Ely

[/pre]

See my small example uploaded.

BTW, back on...

If Range(RangeToSelect) = targetclub Then

You cannot equate two properties (in this case, the property of Range) as if you were equating two values

So this is why you must test the Existence (Is Nothing or Not) of an Intersection of Ranges...

If Not Intersect(Range(RangeToSelect), targetclub) Is Nothing Then

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

City of Ely
>capitalising "of" to "Of" works OK
>but it is not an explanation why "of" fails.

Looks to me that somewhere you compare: "City [highlight #FCE94F]o[/highlight]f Ely" to "City [highlight #FCE94F]O[/highlight]f Ely", and - to computers - those are two different Strings


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[telephoto]

Andy
Valid point, well explained, I was wise to that.
Search variable was set by

targetclub = Application.InputBox("Click on team name.", Type:=2)

Only ever checks what is on the spreadsheet.
Checking City Of Ely =City Of Ely works, Checking City of Ely= City of Ely doesn't work.

Telephoto

 

[Andrzejek]

>Checking City of Ely= City of Ely doesn't work.
You may have either Space(s) somewhere in one or the other "City of Ely", or some unprintable character(s).

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

This is why pictures of data does not help.

This is a waste of time!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

…and I scour your picture for any "of" and unless my eyes deceive me, which is very possible, I find NONE.

So, once again, this is why pictures of data does not help.

This is a waste of time!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[telephoto]

Andy
If that was the case then Ucase() would not work. It did, and was a better workaround, hence your star.

Skip
Apologies if I am not clear enough, or frustrating, neither is intentional.
I have just spent 2 hours trying to get your options to work.
Amending it to one table, had I received it like that, I would just import to Access and process directly.
However I am working with what a valiant 80 yr old is cobbling together and distributing to all the clubs. (It is far easier than his predecessor, who sent out a PDF from Word).

I tried to use the intersect line you provided but the compiler states type mismatch.

I appreciate your attention and thank both of you for your input, using Andy's suggestion works, so going forward I will use that.
Telephoto

 

[telephoto]

Skip
Postings crossed.
Screenshots were taken at the end of a long day, and you saw one where I had been testing using City Ely (which worked). Club name is City of Ely.

Again apologies, in part blame the time difference and a long day which started by taking my daughter for an early train.
T

 

[SkipVought]

In about 3 months, I'll be 80.

"compiler states type mismatch"

What we have here is a failure to communicate. [Cool Hand Luke]

We are having a mixup between RANGES and VALUES. Hence, TYPE mismatch.

But you have a satisfactory solution. All's well.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!