canīt get the else statment right

[peter_swe]

Hello again!
I need help with "else"-statement, pls
I have if statmenif there is a match, then the code writes to ws2
If no , I want it to copy/paste to ws3. The else-code I wrote

Please Login or Register  to view this content.

fail to do this and writes nothing too ws3, but continues to write to ws2. In addition, it takes several seconds longer than before
I enclose the workbook, the working macro is macro1 the not working is Private Sub CommandButton1_Click()Thanks in advance for your help
Best regards
Peter

[peter_swe]

I continue to work on a solution while waiting for help ;-)
Wrote this macro, which shows what I want if the if / elseif statments are false

Please Login or Register  to view this content.

Unfortunatly the code donīt works:
1) copys more then the referee
2) it takes for ever and causes the excel to "not answer"
//Peter

[MarvinP]

Hi Peter,

Hi Peter,

I have another idea to do your work. See the attached and simply change the value in A5 and press enter. It will do an Advanced Filter of all your games showing what Refs need to show up. I've created an Dynamic Named Range of your Lista of all the games so you can add to that list and the filter will still work.

See if this method of doing an Advanced Filter (using VBA) isn't a much easier solution.

BTW - I too officiated many years ago and used Microsoft Access to keep track of games, places and referees.

AdvFilter to get Referees to games.xlsm

[peter_swe]

Hi Marvin and thanks for your answer.
Iīm familliar with advanced filter. In tournamnets where Iīm in charge of referees I use advanced filter. But now Iīm doing this for a friend, and he wanted this in this way.

In Sweden we get the games in Excel, thats why we work in Excel and not in Access.
Why did you stiop officiating?

[MarvinP]

Did you try to change the yellow cell on the second sheet and see how it filters for that ref's schedule?

I officiated back in the late 1970's. I did both basketball and volleyball after teaching High School Math and Science during day. When I left teaching in 1983 to go into the software industry, I stopped officiating. When we moved to Seattle I helped the officials association with an Access db (in 1992) to do scheduling. They had two people who assigned games. One used Paradox (a windows database) and the other used an old DOS program written out of Utah. They never adopted my Access db for scheduling. I understand how many schools, churches, recreation places want officials of all different levels, pay and times. Excel seems like a great way to track this, especially if everyone uses it.

[peter_swe]

Did you try to change the yellow cell on the second sheet and see how it filters for that ref's schedule?

Yes, it works just fine :-)

[bakerman2]

Your Private Sub CommandButton1_Click() works just fine if you take a minut to reread your Else statement.
Every time no match is found you first clear the range on your sheet PO before copying. So in the end it's just the last no match found that is showing on PO.
The solution is to move the clearing of sheet PO just beneath the clearing of sheet Blad2 and you're good to go.

[peter_swe]

thanks for your help

[bakerman2]

You're welcome and thanks for the rep points.

[bakerman2]

Had some time left and added some code that a little faster in execution. Maybe better if list is bigger.
To test fill in name and click first commandbutton. Msgbox will appear with execution time.
Fill in same name and click second commandbutton. Again Msgbox will appear with execution time.