Help with a Repetitive Task

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
586
Office Version
  1. 2013
Platform
  1. Windows
Hi There,

I was wondering if i could get you help with a repetitive task that I do every day. It has been suggested that a VBA macro could do the task.

I have a spreadsheet of many thousands of lines each containing a column with the "Artist and Track Name" (example below). I cut and paste this data into YouTube to check whether the Song already exist.

This can be very tedious and if possible I would like to create a VBA macro that possess each selected cells opening a Youtube Tab in the browser.

Many thanks for help

Sandi Havens - Happy (1980) ~ Great Atlantic
T. J. Johnson - Pretty Lady (1982) ~ Switch
Marcus ft Reginald Gaulden - Love They Don’t Know You (1982) ~ Marcus
Charles Beverly - Don’t You Want A Man Like Me (80s) ~ Vanessa
Gordon Henderson & U Convention - Blame It On Life (1981) [LP:Gordon Henderson & U Convention] ~ Apia
Crack Of Dawn - How Many Times (1981) [LP:Horizons] ~ TTR
Zenith - Freedom Vibrations (1981) [LP:Freedom Vibrations] ~ Lynx International
Rocket - Here Comes My Love (1982) [LP:Rocket] ~ Quality
Chuck Jackson - I’m Your Man ~ {Ace}
Audrey Matthews - I Have No Choice ~ {Kent}
Sharon Scott - It’s Better ~ {Kent}
The Blue Notes - The Girl Makes Me Wanna Sing (1977) [LP:The Truth Has Come To Light] ~ Glades
The Blue Notes - Standing By You Girl (1977) [LP:The Truth Has Come To Light] ~ Glades
Stan Martin - Beautiful Girl (1978) [LP:Stan Martin Is Dr Cool ] ~ RAM
Jimmy Sabater - Memories Of You (1969) [LP:Solo] ~ Tico
Peggy Gaines - Just To Satisfy My Baby (1969) ~ Ref-o-ree
Lee Moses - Free At Last (1971) [LP:Time And Place] ~ Maple
Commands - I’ve Got Love For My Baby (1968) ~ Dynamic
Johnny Moore - Your Love Is Fadin’ (1969) ~ Mercury
Fortson & Scott - Sweet Lover (1968) ~ Pzazz
Ron And Candy - Lovely Weekend (1973) ~ Inner City
Ray Lewis - Too Sweet To Be Lonely (1969) ~ D’ar Recording Company
Steelers - A Thousand Tomorrows (1970) ~ Epic
Clyde McPhatter - Anyone Can Tell (We’re In Love) (1970) [LP:Welcome Home ] ~ MCA
5 Wagers - Come And Ask Me (1974) ~ Tiara
Bottom & Company - Gonna Find A True Love (1974) ~ Motown
The Newcomers - The Whole World’s A Picture Show (1974) ~ Truth
Ann Sexton - You’ve Been Gone Too Long (1971) ~ Impel
Bull - Bygones (1972) ~ Bell
Four Tops - Clip My Wings ~ Motown
Oscar Perry - Let Me Grow Old With You (1975) ~ Crazy Cajun
Maxine Brown - Bella Mia (1973) ~ Avco Embassy
The Vee Gees - Talkin’ (1974) ~ Jump Off
The Eliminators - Loving Explosion (1974) [LP:Loving Explosion] ~ Zafiro
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try if this works for you

Book1
ABC
1Sandi Havens - Happy (1980) ~ Great Atlantichttps://www.youtube.com/results?search_query=Sandi+Havens+-+Happy+(1980)+~+Great+Atlantic
2T. J. Johnson - Pretty Lady (1982) ~ Switchhttps://www.youtube.com/results?search_query=T.+J.+Johnson+-+Pretty+Lady+(1982)+~+Switch
3Marcus ft Reginald Gaulden - Love They Don’t Know You (1982) ~ Marcushttps://www.youtube.com/results?search_query=Marcus+ft+Reginald+Gaulden+-+Love+They+Don’t+Know+You+(1982)+~+Marcus
4Charles Beverly - Don’t You Want A Man Like Me (80s) ~ Vanessahttps://www.youtube.com/results?search_query=Charles+Beverly+-+Don’t+You+Want+A+Man+Like+Me+(80s)+~+Vanessa
5Gordon Henderson & U Convention - Blame It On Life (1981) [LP:Gordon Henderson & U Convention] ~ Apiahttps://www.youtube.com/results?search_query=Gordon+Henderson+%26+U+Convention+-+Blame+It+On+Life+(1981)+%5BLP%3AGordon+Henderson+%26+U+Convention%5D+~+Apia
6Crack Of Dawn - How Many Times (1981) [LP:Horizons] ~ TTRhttps://www.youtube.com/results?search_query=Crack+Of+Dawn+-+How+Many+Times+(1981)+%5BLP%3AHorizons%5D+~+TTR
7Zenith - Freedom Vibrations (1981) [LP:Freedom Vibrations] ~ Lynx Internationalhttps://www.youtube.com/results?search_query=Zenith+-+Freedom+Vibrations+(1981)+%5BLP%3AFreedom+Vibrations%5D+~+Lynx+International
8Rocket - Here Comes My Love (1982) [LP:Rocket] ~ Qualityhttps://www.youtube.com/results?search_query=Rocket+-+Here+Comes+My+Love+(1982)+%5BLP%3ARocket%5D+~+Quality
9Chuck Jackson - I’m Your Man ~ {Ace}https://www.youtube.com/results?search_query=Chuck+Jackson+-+I’m+Your+Man+~+{Ace}
10Audrey Matthews - I Have No Choice ~ {Kent}https://www.youtube.com/results?search_query=Audrey+Matthews+-+I+Have+No+Choice+~+{Kent}
11Sharon Scott - It’s Better ~ {Kent}https://www.youtube.com/results?search_query=Sharon+Scott+-+It’s+Better+~+{Kent}
12The Blue Notes - The Girl Makes Me Wanna Sing (1977) [LP:The Truth Has Come To Light] ~ Gladeshttps://www.youtube.com/results?search_query=The+Blue+Notes+-+The+Girl+Makes+Me+Wanna+Sing+(1977)+%5BLP%3AThe+Truth+Has+Come+To+Light%5D+~+Glades
13The Blue Notes - Standing By You Girl (1977) [LP:The Truth Has Come To Light] ~ Gladeshttps://www.youtube.com/results?search_query=The+Blue+Notes+-+Standing+By+You+Girl+(1977)+%5BLP%3AThe+Truth+Has+Come+To+Light%5D+~+Glades
14Stan Martin - Beautiful Girl (1978) [LP:Stan Martin Is Dr Cool ] ~ RAMhttps://www.youtube.com/results?search_query=Stan+Martin+-+Beautiful+Girl+(1978)+%5BLP%3AStan+Martin+Is+Dr+Cool+%5D+~+RAM
15Jimmy Sabater - Memories Of You (1969) [LP:Solo] ~ Ticohttps://www.youtube.com/results?search_query=Jimmy+Sabater+-+Memories+Of+You+(1969)+%5BLP%3ASolo%5D+~+Tico
16Peggy Gaines - Just To Satisfy My Baby (1969) ~ Ref-o-reehttps://www.youtube.com/results?search_query=Peggy+Gaines+-+Just+To+Satisfy+My+Baby+(1969)+~+Ref-o-ree
17Lee Moses - Free At Last (1971) [LP:Time And Place] ~ Maplehttps://www.youtube.com/results?search_query=Lee+Moses+-+Free+At+Last+(1971)+%5BLP%3ATime+And+Place%5D+~+Maple
18Commands - I’ve Got Love For My Baby (1968) ~ Dynamichttps://www.youtube.com/results?search_query=Commands+-+I’ve+Got+Love+For+My+Baby+(1968)+~+Dynamic
19Johnny Moore - Your Love Is Fadin’ (1969) ~ Mercuryhttps://www.youtube.com/results?search_query=Johnny+Moore+-+Your+Love+Is+Fadin’+(1969)+~+Mercury
20Fortson & Scott - Sweet Lover (1968) ~ Pzazzhttps://www.youtube.com/results?search_query=Fortson+%26+Scott+-+Sweet+Lover+(1968)+~+Pzazz
21Ron And Candy - Lovely Weekend (1973) ~ Inner Cityhttps://www.youtube.com/results?search_query=Ron+And+Candy+-+Lovely+Weekend+(1973)+~+Inner+City
22Ray Lewis - Too Sweet To Be Lonely (1969) ~ D’ar Recording Companyhttps://www.youtube.com/results?search_query=Ray+Lewis+-+Too+Sweet+To+Be+Lonely+(1969)+~+D’ar+Recording+Company
23Steelers - A Thousand Tomorrows (1970) ~ Epichttps://www.youtube.com/results?search_query=Steelers+-+A+Thousand+Tomorrows+(1970)+~+Epic
24Clyde McPhatter - Anyone Can Tell (We’re In Love) (1970) [LP:Welcome Home ] ~ MCAhttps://www.youtube.com/results?search_query=Clyde+McPhatter+-+Anyone+Can+Tell+(We’re+In+Love)+(1970)+%5BLP%3AWelcome+Home+%5D+~+MCA
255 Wagers - Come And Ask Me (1974) ~ Tiarahttps://www.youtube.com/results?search_query=5+Wagers+-+Come+And+Ask+Me+(1974)+~+Tiara
26Bottom & Company - Gonna Find A True Love (1974) ~ Motownhttps://www.youtube.com/results?search_query=Bottom+%26+Company+-+Gonna+Find+A+True+Love+(1974)+~+Motown
27The Newcomers - The Whole World’s A Picture Show (1974) ~ Truthhttps://www.youtube.com/results?search_query=The+Newcomers+-+The+Whole+World’s+A+Picture+Show+(1974)+~+Truth
28Ann Sexton - You’ve Been Gone Too Long (1971) ~ Impelhttps://www.youtube.com/results?search_query=Ann+Sexton+-+You’ve+Been+Gone+Too+Long+(1971)+~+Impel
29Bull - Bygones (1972) ~ Bellhttps://www.youtube.com/results?search_query=Bull+-+Bygones+(1972)+~+Bell
30Four Tops - Clip My Wings ~ Motownhttps://www.youtube.com/results?search_query=Four+Tops+-+Clip+My+Wings+~+Motown
31Oscar Perry - Let Me Grow Old With You (1975) ~ Crazy Cajunhttps://www.youtube.com/results?search_query=Oscar+Perry+-+Let+Me+Grow+Old+With+You+(1975)+~+Crazy+Cajun
32Maxine Brown - Bella Mia (1973) ~ Avco Embassyhttps://www.youtube.com/results?search_query=Maxine+Brown+-+Bella+Mia+(1973)+~+Avco+Embassy
33The Vee Gees - Talkin’ (1974) ~ Jump Offhttps://www.youtube.com/results?search_query=The+Vee+Gees+-+Talkin’+(1974)+~+Jump+Off
34The Eliminators - Loving Explosion (1974) [LP:Loving Explosion] ~ Zafirohttps://www.youtube.com/results?search_query=The+Eliminators+-+Loving+Explosion+(1974)+%5BLP%3ALoving+Explosion%5D+~+Zafiro
35
36
Sheet1
Cell Formulas
RangeFormula
B1:B34B1=HYPERLINK(CONCATENATE("https://www.youtube.com/results?search_query=",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","+"),"&","%26"),"[","%5B"),":","%3A"),"]","%5D")))
 
Upvote 0
Try if this works for you

Book1
ABC
1Sandi Havens - Happy (1980) ~ Great Atlantichttps://www.youtube.com/results?search_query=Sandi+Havens+-+Happy+(1980)+~+Great+Atlantic
2T. J. Johnson - Pretty Lady (1982) ~ Switchhttps://www.youtube.com/results?search_query=T.+J.+Johnson+-+Pretty+Lady+(1982)+~+Switch
3Marcus ft Reginald Gaulden - Love They Don’t Know You (1982) ~ Marcushttps://www.youtube.com/results?search_query=Marcus+ft+Reginald+Gaulden+-+Love+They+Don’t+Know+You+(1982)+~+Marcus
4Charles Beverly - Don’t You Want A Man Like Me (80s) ~ Vanessahttps://www.youtube.com/results?search_query=Charles+Beverly+-+Don’t+You+Want+A+Man+Like+Me+(80s)+~+Vanessa
5Gordon Henderson & U Convention - Blame It On Life (1981) [LP:Gordon Henderson & U Convention] ~ Apiahttps://www.youtube.com/results?search_query=Gordon+Henderson+%26+U+Convention+-+Blame+It+On+Life+(1981)+%5BLP%3AGordon+Henderson+%26+U+Convention%5D+~+Apia
6Crack Of Dawn - How Many Times (1981) [LP:Horizons] ~ TTRhttps://www.youtube.com/results?search_query=Crack+Of+Dawn+-+How+Many+Times+(1981)+%5BLP%3AHorizons%5D+~+TTR
7Zenith - Freedom Vibrations (1981) [LP:Freedom Vibrations] ~ Lynx Internationalhttps://www.youtube.com/results?search_query=Zenith+-+Freedom+Vibrations+(1981)+%5BLP%3AFreedom+Vibrations%5D+~+Lynx+International
8Rocket - Here Comes My Love (1982) [LP:Rocket] ~ Qualityhttps://www.youtube.com/results?search_query=Rocket+-+Here+Comes+My+Love+(1982)+%5BLP%3ARocket%5D+~+Quality
9Chuck Jackson - I’m Your Man ~ {Ace}https://www.youtube.com/results?search_query=Chuck+Jackson+-+I’m+Your+Man+~+{Ace}
10Audrey Matthews - I Have No Choice ~ {Kent}https://www.youtube.com/results?search_query=Audrey+Matthews+-+I+Have+No+Choice+~+{Kent}
11Sharon Scott - It’s Better ~ {Kent}https://www.youtube.com/results?search_query=Sharon+Scott+-+It’s+Better+~+{Kent}
12The Blue Notes - The Girl Makes Me Wanna Sing (1977) [LP:The Truth Has Come To Light] ~ Gladeshttps://www.youtube.com/results?search_query=The+Blue+Notes+-+The+Girl+Makes+Me+Wanna+Sing+(1977)+%5BLP%3AThe+Truth+Has+Come+To+Light%5D+~+Glades
13The Blue Notes - Standing By You Girl (1977) [LP:The Truth Has Come To Light] ~ Gladeshttps://www.youtube.com/results?search_query=The+Blue+Notes+-+Standing+By+You+Girl+(1977)+%5BLP%3AThe+Truth+Has+Come+To+Light%5D+~+Glades
14Stan Martin - Beautiful Girl (1978) [LP:Stan Martin Is Dr Cool ] ~ RAMhttps://www.youtube.com/results?search_query=Stan+Martin+-+Beautiful+Girl+(1978)+%5BLP%3AStan+Martin+Is+Dr+Cool+%5D+~+RAM
15Jimmy Sabater - Memories Of You (1969) [LP:Solo] ~ Ticohttps://www.youtube.com/results?search_query=Jimmy+Sabater+-+Memories+Of+You+(1969)+%5BLP%3ASolo%5D+~+Tico
16Peggy Gaines - Just To Satisfy My Baby (1969) ~ Ref-o-reehttps://www.youtube.com/results?search_query=Peggy+Gaines+-+Just+To+Satisfy+My+Baby+(1969)+~+Ref-o-ree
17Lee Moses - Free At Last (1971) [LP:Time And Place] ~ Maplehttps://www.youtube.com/results?search_query=Lee+Moses+-+Free+At+Last+(1971)+%5BLP%3ATime+And+Place%5D+~+Maple
18Commands - I’ve Got Love For My Baby (1968) ~ Dynamichttps://www.youtube.com/results?search_query=Commands+-+I’ve+Got+Love+For+My+Baby+(1968)+~+Dynamic
19Johnny Moore - Your Love Is Fadin’ (1969) ~ Mercuryhttps://www.youtube.com/results?search_query=Johnny+Moore+-+Your+Love+Is+Fadin’+(1969)+~+Mercury
20Fortson & Scott - Sweet Lover (1968) ~ Pzazzhttps://www.youtube.com/results?search_query=Fortson+%26+Scott+-+Sweet+Lover+(1968)+~+Pzazz
21Ron And Candy - Lovely Weekend (1973) ~ Inner Cityhttps://www.youtube.com/results?search_query=Ron+And+Candy+-+Lovely+Weekend+(1973)+~+Inner+City
22Ray Lewis - Too Sweet To Be Lonely (1969) ~ D’ar Recording Companyhttps://www.youtube.com/results?search_query=Ray+Lewis+-+Too+Sweet+To+Be+Lonely+(1969)+~+D’ar+Recording+Company
23Steelers - A Thousand Tomorrows (1970) ~ Epichttps://www.youtube.com/results?search_query=Steelers+-+A+Thousand+Tomorrows+(1970)+~+Epic
24Clyde McPhatter - Anyone Can Tell (We’re In Love) (1970) [LP:Welcome Home ] ~ MCAhttps://www.youtube.com/results?search_query=Clyde+McPhatter+-+Anyone+Can+Tell+(We’re+In+Love)+(1970)+%5BLP%3AWelcome+Home+%5D+~+MCA
255 Wagers - Come And Ask Me (1974) ~ Tiarahttps://www.youtube.com/results?search_query=5+Wagers+-+Come+And+Ask+Me+(1974)+~+Tiara
26Bottom & Company - Gonna Find A True Love (1974) ~ Motownhttps://www.youtube.com/results?search_query=Bottom+%26+Company+-+Gonna+Find+A+True+Love+(1974)+~+Motown
27The Newcomers - The Whole World’s A Picture Show (1974) ~ Truthhttps://www.youtube.com/results?search_query=The+Newcomers+-+The+Whole+World’s+A+Picture+Show+(1974)+~+Truth
28Ann Sexton - You’ve Been Gone Too Long (1971) ~ Impelhttps://www.youtube.com/results?search_query=Ann+Sexton+-+You’ve+Been+Gone+Too+Long+(1971)+~+Impel
29Bull - Bygones (1972) ~ Bellhttps://www.youtube.com/results?search_query=Bull+-+Bygones+(1972)+~+Bell
30Four Tops - Clip My Wings ~ Motownhttps://www.youtube.com/results?search_query=Four+Tops+-+Clip+My+Wings+~+Motown
31Oscar Perry - Let Me Grow Old With You (1975) ~ Crazy Cajunhttps://www.youtube.com/results?search_query=Oscar+Perry+-+Let+Me+Grow+Old+With+You+(1975)+~+Crazy+Cajun
32Maxine Brown - Bella Mia (1973) ~ Avco Embassyhttps://www.youtube.com/results?search_query=Maxine+Brown+-+Bella+Mia+(1973)+~+Avco+Embassy
33The Vee Gees - Talkin’ (1974) ~ Jump Offhttps://www.youtube.com/results?search_query=The+Vee+Gees+-+Talkin’+(1974)+~+Jump+Off
34The Eliminators - Loving Explosion (1974) [LP:Loving Explosion] ~ Zafirohttps://www.youtube.com/results?search_query=The+Eliminators+-+Loving+Explosion+(1974)+%5BLP%3ALoving+Explosion%5D+~+Zafiro
35
36
Sheet1
Cell Formulas
RangeFormula
B1:B34B1=HYPERLINK(CONCATENATE("https://www.youtube.com/results?search_query=",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","+"),"&","%26"),"[","%5B"),":","%3A"),"]","%5D")))
Hi A Durfani

Thanks you so much for your prompt reply.

I have tested the formula and it works with one issue.
Everytime I click the link I get the message below, requiring me to accept the YouTube agreement which adds to me an unnecessary step.
When I do the process manually I do not get this screen.
Is there a way around this screen?
YouTube Message.PNG
 
Upvote 0
Hi A Durfani

Thanks you so much for your prompt reply.

I have tested the formula and it works with one issue.
Everytime I click the link I get the message below, requiring me to accept the YouTube agreement which adds to me an unnecessary step.
When I do the process manually I do not get this screen.
Is there a way around this screen?
View attachment 39547
Hi A Durfani

I have managed to find a solution with a setting in chrome.

Thanks again for all your help.

Regards

Desmond
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top