VBA code for multiple searches and paste values

AGU

New Member
Joined
Apr 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi, Hope you all are doing great. I am in a dire need of someone's help in excel vba. I have two data files, ok. 1) RPM and 2) Test data.
In last available column in Test Data file I require rate from RPM data sheet. I am a learner of Excel Programming, as in, new to it. My search criteria is based on 1) Channel, 2) Data point in between start time and end time, and 3) Day of the week. Can anyone help me out for creating the excel macro. Its a large data cannot do it manually :(
Both data files are attached.

Thanking you in anticipation.

Test Data

Test Data.xlsx
ABCDEFGHIJKL
1DateTimeChannelDayCategoryTransmissionHourTimeBandAd_Duration_in_MinsMinsAd_SlotcommercialMarkID
225.09.202019:02:51EntertainmentFridayCullinary19Access Primetim00:00:350.58Third2.00925E+14
325.09.202019:20:14EntertainmentFridayCullinary19Access Primetim00:00:350.58Second2.00925E+14
425.09.202020:27:24EntertainmentFridayCullinary20Primetime00:00:350.58Other2.00925E+14
525.09.202020:42:12EntertainmentFridayCullinary20Primetime00:00:150.25last2.00925E+14
625.09.202022:20:10EntertainmentFridayCullinary22Late Primetime00:00:350.58Second2.00925E+14
725.09.202022:27:08EntertainmentFridayCullinary22Late Primetime00:00:350.58Other2.00925E+14
826.09.202002:16:44EntertainmentSaturdayCullinary2Night00:00:060.1002.00926E+14
926.09.202002:19:30EntertainmentSaturdayCullinary2Night00:00:060.1002.00926E+14
1026.09.202002:29:15EntertainmentSaturdayCullinary2Night00:00:060.1002.00926E+14
1126.09.202002:31:51EntertainmentSaturdayCullinary2Night00:00:060.1002.00926E+14
1226.09.202002:40:14EntertainmentSaturdayCullinary2Night00:00:050.0802.00926E+14
1326.09.202002:42:45EntertainmentSaturdayCullinary2Night00:00:060.1002.00926E+14
1426.09.202002:52:29EntertainmentSaturdayCullinary2Night00:00:050.0802.00926E+14
1526.09.202002:55:07EntertainmentSaturdayCullinary2Night00:00:060.1002.00926E+14
1626.09.202003:16:45EntertainmentSaturdayCullinary3Night00:00:050.0802.00926E+14
1726.09.202003:19:16EntertainmentSaturdayCullinary3Night00:00:050.0802.00926E+14
1826.09.202003:27:13EntertainmentSaturdayCullinary3Night00:00:060.1002.00926E+14
1926.09.202003:29:24EntertainmentSaturdayCullinary3Night00:00:060.1002.00926E+14
2026.09.202003:39:08EntertainmentSaturdayCullinary3Night00:00:050.0802.00926E+14
2126.09.202003:42:25EntertainmentSaturdayCullinary3Night00:00:060.1002.00926E+14
2226.09.202003:54:29EntertainmentSaturdayCullinary3Night00:00:050.0802.00926E+14
2326.09.202003:57:00EntertainmentSaturdayCullinary3Night00:00:060.1002.00926E+14
2426.09.202004:17:53EntertainmentSaturdayCullinary4Night00:00:050.0802.00926E+14
2526.09.202004:20:14EntertainmentSaturdayCullinary4Night00:00:060.1002.00926E+14
2626.09.202004:28:46EntertainmentSaturdayCullinary4Night00:00:050.0802.00926E+14
2726.09.202004:30:59EntertainmentSaturdayCullinary4Night00:00:060.1002.00926E+14
2826.09.202004:42:22EntertainmentSaturdayCullinary4Night00:00:050.0802.00926E+14
2926.09.202004:45:08EntertainmentSaturdayCullinary4Night00:00:060.1002.00926E+14
3026.09.202004:52:56EntertainmentSaturdayCullinary4Night00:00:050.0802.00926E+14
3126.09.202004:55:51EntertainmentSaturdayCullinary4Night00:00:060.1002.00926E+14
3226.09.202005:10:53EntertainmentSaturdayCullinary5Night00:00:050.0802.00926E+14
3326.09.202005:13:21EntertainmentSaturdayCullinary5Night00:00:060.1002.00926E+14
3426.09.202005:23:21EntertainmentSaturdayCullinary5Night00:00:050.0802.00926E+14
3526.09.202005:25:19EntertainmentSaturdayCullinary5Night00:00:060.1002.00926E+14
3626.09.202005:38:54EntertainmentSaturdayCullinary5Night00:00:050.0802.00926E+14
3726.09.202005:41:12EntertainmentSaturdayCullinary5Night00:00:060.1002.00926E+14
3826.09.202005:52:08EntertainmentSaturdayCullinary5Night00:00:050.0802.00926E+14
3926.09.202005:54:05EntertainmentSaturdayCullinary5Night00:00:060.1002.00926E+14
4026.09.202006:08:09EntertainmentSaturdayCullinary6Early Morning00:00:050.0802.00926E+14
4126.09.202006:08:37EntertainmentSaturdayCullinary6Early Morning00:00:050.0802.00926E+14
4226.09.202006:21:58EntertainmentSaturdayCullinary6Early Morning00:00:050.0802.00926E+14
4326.09.202006:22:25EntertainmentSaturdayCullinary6Early Morning00:00:060.1002.00926E+14
4426.09.202006:33:44EntertainmentSaturdayCullinary6Early Morning00:00:050.0802.00926E+14
4526.09.202006:34:12EntertainmentSaturdayCullinary6Early Morning00:00:050.0802.00926E+14
4626.09.202006:55:28EntertainmentSaturdayCullinary6Early Morning00:00:050.0802.00926E+14
Test Data



RPM (Day of the Week):

RPM (Day of the Week).xlsx
ABCDEFGHIJ
1Start timeEnd TimeChannelMondayTuesdayWednesdayThursdayFridaySaturdaySunday
200:00:0000:30:00Entertainment5300530053005300530053005300
300:30:0001:00:00Entertainment5300530053005300530053005300
401:00:0001:30:00Entertainment5300530053005300530053005300
501:30:0002:00:00Entertainment5300530053005300530053005300
602:00:0002:30:00Entertainment1475147514751475147514751475
702:30:0003:00:00Entertainment1475147514751475147514751475
803:00:0003:30:00Entertainment1475147514751475147514751475
903:30:0004:00:00Entertainment1475147514751475147514751475
1004:00:0004:30:00Entertainment1475147514751475147514751475
1104:30:0005:00:00Entertainment1475147514751475147514751475
1205:00:0005:30:00Entertainment1475147514751475147514751475
1305:30:0006:00:00Entertainment1475147514751475147514751475
1406:00:0006:30:00Entertainment1475147514751475147514751475
1506:30:0007:00:00Entertainment1475147514751475147514751475
1607:00:0007:30:00Entertainment1475147514751475147514751475
1707:30:0008:00:00Entertainment1475147514751475147514751475
1808:00:0008:30:00Entertainment2750275027502750275027502750
1908:30:0009:00:00Entertainment2750275027502750275027502750
2009:00:0009:30:00Entertainment11250112501125011250112501125011250
2109:30:0010:00:00Entertainment11250112501125011250112501125011250
2210:00:0010:30:00Entertainment11250112501125011250112501125011250
2310:30:0011:00:00Entertainment11250112501125011250112501125011250
2411:00:0011:30:00Entertainment11250112501125011250112501125011250
2511:30:0012:00:00Entertainment11250112501125011250112501125011250
2612:00:0012:30:00Entertainment11250112501125011250112501125011250
2712:30:0013:00:00Entertainment11250112501125011250112501125011250
2813:00:0013:30:00Entertainment12000120001200012000140001400014000
2913:30:0014:00:00Entertainment12000120001200012000140001400014000
3014:00:0014:30:00Entertainment12000120001200012000140001400014000
3114:30:0015:00:00Entertainment12000120001200012000140001400014000
3215:00:0015:30:00Entertainment12000120001200012000140001400014000
3315:30:0016:00:00Entertainment12000120001200012000140001400014000
3416:00:0016:30:00Entertainment12000120001200012000140001400014000
3516:30:0017:00:00Entertainment12000120001200012000140001400014000
3617:00:0017:30:00Entertainment12000120001200012000140001400014000
3717:30:0018:00:00Entertainment12000120001200012000140001400014000
3818:00:0018:30:00Entertainment18000180001800018000180001800018000
3918:30:0019:00:00Entertainment18000180001800018000180001800018000
4019:00:0019:30:00Entertainment38000380003800038000380003800038000
4119:30:0020:00:00Entertainment38000380003800038000380003800038000
4220:00:0020:30:00Entertainment192000192000192000192000240000240000240000
4320:30:0021:00:00Entertainment192000192000192000192000240000240000240000
4421:00:0021:30:00Entertainment103500103500103500103500103500103500103500
4521:30:0022:00:00Entertainment103500103500103500103500103500103500103500
4622:00:0022:30:00Entertainment46100461004610046100461004610046100
4722:30:0023:00:00Entertainment46100461004610046100461004610046100
4823:00:0023:30:00Entertainment30800308003080030800308003080030800
4923:30:0000:00:00Entertainment30800308003080030800308003080030800
RPM
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
944
Office Version
  1. 2010
Platform
  1. Windows
Hi,​
you must read the forums rules in particular for cross posting …​
 

AGU

New Member
Joined
Apr 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi,​
you must read the forums rules in particular for cross posting …​
I didn't find the rules, sorry. Can you help me now please?!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,585
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA code for multiple searches and paste values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

AGU

New Member
Joined
Apr 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA code for multiple searches and paste values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Thanks for a warm welcome.
smile.gif

I apologize for not going through the rules, if I had learnt rules I would not have definitely cross posted my post or in other case would have provided link of cross-post. I am here to seek help, but not breaking forum rules. Again sorry that I overlooked forum rules and end up mayhap hurting you people. I have apologized there and apologize here as well. I hope I have made myself clear, I just made a boob! Hope you've forgiven this mistake & will help me out in my query!
 

AGU

New Member
Joined
Apr 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi Fluff,
You're a moderator I need your help
Please let me know if I need to repost my query again as no one seems to help in this thread. Kindly respond.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,619
Messages
5,659,913
Members
418,536
Latest member
Tezzies

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