VBA code for multiple searches and paste values

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. I could have used vlookup if there had one search criteria. In my files there are three criterion required. 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. If data would be short I could have done it manually, but its a very large data of 20,000 plus entries.
Both data files are attached.

Thanking you in anticipation.

You could do this with a formula. It may be a little sluggish over 20,000 rows of data but bearable when the alternative is manually working it out and not much different in time to a VBA solution.

With both workbooks open enter the below formula in L2 on the Test Data workbook and copy down.

Formula:

Please Login or Register  to view this content.

BSB

It is working fine, thanks. If I get a VBA code then it'll be a great help!

Simply apply the same formula using VBA then stamp the result as values afterward.

Please Login or Register  to view this content.

BSB

Thanks. This will help me out partially. I appreciate your support, yet my objective hasn't been completed. Because in my data last column isn't fixed it can be L or it can be any other column. I already mentioned in my very first post, my requirements are 'last empty available column ' in my data file. And this VBA code is very slow, because my data has 50,000 plus rows.

Thanks for a warm welcome.
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 hope I have made myself clear, I just made a boob! Hope you've forgiven this mistake & will help me out in my query!

Abida Gohar,
See if this is faster.
Assuming both files are in the same folder.

Please Login or Register  to view this content.

Hi Jindon,
Thanks for responding. Actually, my both files are in the same folder, but what I'm going to do is while applying code my both files will be open, 1) RPM file & 2) Data File. Your code is working fine and its fast as well. What problem I'm facing is column header is missing & if any of the search criteria's column (1. channel, 2. time, 3. day of the week) is missing, yet it is applying the RPM which should not happen. If any search criteria is missing it must show error, because for accuracy it must work with all 3 columns. If any column is missing and it's applying RPM (rate per minute), then it will cause a problem for me. My whole budget calculation will be wrong.
I appreciate your help.. I will be grateful if you resolve my issue.

Regards,
AG

What problem I'm facing is column header is missing

What is the heading?

if any of the search criteria's column (1. channel, 2. time, 3. day of the week) is missing, yet it is applying the RPM

Where did you see this in you uploaded file?

Header is RPM.

The code which you've sent is applying RPM on required searched criteria. If I delete any of the search criteria columns in data file your code is still applying RPM. Instead the code should give error that something is missing.
My search criterias: 1) Channel, 2) Data point in between start time and end time, and 3) Day of the week

Of course, the code opens RPM workbook each time you run and not assuming RPM is open.
Change to

Please Login or Register  to view this content.

Originally Posted by jindon

Of course, the code opens RPM workbook each time you run and not assuming RPM is open.
Change to

Please Login or Register  to view this content.

Hi Jindon, I am very thankful for your help. Your code works awesome. It has resolved my major concern. There are few more things that may switch up this code.
1) Can we remove the RPM file path and if RPM file does not open it opens msg box and informs required file is missing. 2) Can we add its column heading "buying RPM" and when the code would start if "buying RPM" column is already present in the data, a message box opens and informs and ask for continuation.

Thank you so very much, you helped me aloy, you're Excel VBA genius, God Bless You!

1)
Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

2) So, when "buying RPM" is already in the header, overwrite the column...

Please Login or Register  to view this content.

Thanks a lot Jindon.
I am stuck in one thing .. After finalizing the code I just realized I forgot to mention that my test data file name will also change every week. I am really sorry I didn’t mention that in previous discussions just by mistake. This name was kept for experiment, this isn’t an actual name. Actual name can be whatsoever according to the vendor. Until now, you’ve been so helpful and kind enough to help me throughout. I know I really bothered you and I’m bothering you again and thanks for being patient with me.

It could be done if file name has some kind of pattern, otherwise how do you find its name?

Hi, thanks for replying. Actually, file name could be any (for eg., category data, brand wise data, spot wise tracking, etc.). I am planning to have two files open when we we will be applying code, one is macro and other one is data file ( data sheet will always be sheet 1). The pattern that I have of data file will always be sheet 1.

Do you mean it would be the one if any other workbook is open and it is the only one workbook other than the one the code is running?
But how do you find the workbook name to open when it is not open?

Originally Posted by jindon

Do you mean it would be the one if any other workbook is open and it is the only one workbook other than the one the code is running?
But how do you find the workbook name to open when it is not open?

Two files will be open simultaneously. One is my data file and other one is macro running file. In my data file my data sheet will always be sheet1 and first 4 columns will always be date, time, channel & day.
If these columns are not found in an open data file sheet one it shows error.

Originally Posted by Abida Gohar

In my data file my data sheet will always be sheet1 and first 4 columns will always be date, time, channel & day.

1) Are you sure about the sheet name? you said Sheet 1 in the post #17.
2) Also your current data sheet has Start time, End Time, Channel

Need correct information.

1) I'm sure file name can be any, but sheet will always be the workbook's first sheet.

2)
RPM (Day of the Week) files have below columns (snap shot is also attached)

Start time, End Time, Channel, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday


Data files have below first 4 columns (snap shot is also attached)

Date, Time, Channel, Day

I want to apply "Buying RMPs" on data files taken from "RPM (Day of the Week)"

I have two data files, ok. 1) RPM file and 2) data file. In last available column in Data file I require RPM from RPM sheet. based on three criterion. 1) Channel, 2) Data point in between start time and end time, and 3) Day of the week.

Dim rng2 As Range
Dim looop, row1, row2, rownum, time_col, new_col As Integer
Dim chanel, chnl_RateCard, the_time, st_RateCard, end_RateCard, Rate, Rate_RateCard As Integer

Set rng2 = Workbooks("RPM (Day of the Week).xlsx").Sheets("RPM").Range("A1").CurrentRegion

For looop = 1 To 1 'Worksheets.Count ' Main Loop for sheet Change

Worksheets(looop).Activate

the_time = FindCol("Time")
chanel = FindCol("Channel")

chnl_RateCard = 3 '//In RPM Sheet Columns must be
st_RateCard = 1 '//hard Coded RateCard Column Numbers
end_RateCard = 2 '//specified in this box
Rate_RateCard = 4 '


If FindCol("Buying RPM") = 0 Then

new_col = allocate_col("Buying RPM")
Rate = new_col

row1 = 2
While Not IsEmpty(Cells(row1, 1))

For row2 = 2 To 2600

If LCase(Cells(row1, chanel)) = LCase(rng2.Cells(row2, chnl_RateCard)) Then
If (Cells(row1, the_time) >= rng2.Cells(row2, st_RateCard)) And (Cells(row1, the_time) < rng2.Cells(row2, end_RateCard)) Then

Cells(row1, new_col) = rng2.Cells(row2, Rate_RateCard).Value
Exit For
End If
End If
Next row2


row1 = row1 + 1
Wend

End If

Next looop
Exit Sub


Above is the code that I'm using previously, but it has an issue it doesn't bring RPM on the basis of day of the week.

1)
Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

2)
This will check
1) If other workbook(s) is/are open.
2) If none or more than 1 (excluding Test Data itself) are open, the code will ask you to select workbook,
3) Will check if workbook has required heading in the first sheet.

Please Login or Register  to view this content.

Sorry Jindon above code isn't working. I am attaching 2 snapshots. First snapshot has the actual scenario when I was running the code means all my required files were opened & I was running the code. This scenario will remain whenever I do this exercise. In second screenshot, when I ran the above given code it showed a message box when I clicked ok new window appeared to browse the required file open.

That's working.

2) If none or more than 1 (excluding Test Data itself) are open, the code will ask you to select workbook,

When multiple other workbooks are open, how do you determine the right one to use without knowing the file name?

There are multiple workbooks: RPM, Data file and macro. Macro is going to run, pick up rate from RPM and put it on data file.
What do you suggest, if we fix file name, every time I will have to rename file and sheet.
I simply want to have one code file with a button and 2 other files rpm and data. When I press button rates from RPM simply apply to data file.
Maybe possible its not the case in VBA, I assume?! Correct me if I'm wrong.

Thanks again for your cooperation, and sorry I bother you alot 😌🙂

Are running the code from "Macro" workbook?

Yes from file name VBA macro.

OK,
Do you need "Data" workbook to be open each time you run the code?

Yes, if it remains open its well and good & easy for me. And if it doesn't then at least it fulfills my objective. What do you say?

Sorry, I meant to say "RPM" workbook need to be open?

No, not necessary.. it's alright don't mention..

OK,
Run the code from the workbook named "VAB Macro.xlsm" and output to Sheet1 in "Data.xlsx?" based on the data in RPM sheet of unknown filename.

Or does sheet name "RPM" in unknown workbook also change?

I ran the code it says data.xlsx is not correct workbook.
RPM sheet doesn't change neither its name. Its name will always be RPM (day of the week) and its sheet name will always be RPM.

Yes, because I assumed that the code would be run from "Data.xlsm" itself...
So the current code is counting 3 workbooks are open where it should be only 2.
Attached is the one I meant to use.
You need to close "VBA Macro" workbook before you run.

Yes I ran.
It is showing run time error 9 and subscript out of range.

Ahhh, that was

Please Login or Register  to view this content.

need to be

Please Login or Register  to view this content.

Yes its works with a little change in line
With ThisWorkbook.Sheets("sheet1")

OK, and now you want to run from "VBA Macro" workbook?

Then I need a correct file extension of "Data.xls(x/m/b)"

So that the code should
1) Check if Data.xls(x/m/b) is open. If not ask to open.(show file selection browser)
2) Check if other workbook(s) other than 1) & "VBA Macro" is/are open.
3) do the verification of the correct workbook and ask to select workbook, if any of the open workbook is not valid data.
and so on.

Yes my requirment is run this code from "VBA Macro.xlsm" workbook and File extension of Data file will always be ".xlsx"

“run-time error 13 – Type mismatch” at below line
txt = Join(Array(x(2, ii), myWD(i - 2)), Chr(2))

No error.....

Sorry Jindon, still not working. I am trying to attach screen recording, but facing file size issue.

How is it not working?

1) When "Data.xlsx" is not open, you need to select "Data.xlsx"
2) You need to select "RPM".

If you are getting that kind of error, you probably select wrong workbook at 2).

Did you try my attachment?
It is working here.

I don't know. Right now I'm using 3 files the one you sent to me.
see attached screen shots

another scenario

Hummm, no idea...
Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

And see how it goes...

Still got the same error in all scenarios

Jindon I think we lost in this coding.

Why not start a new code by taking few things into consideration.

Objective
Need Rate Per Minute (RPM) on Data workbook In the last available empty column.
(Note: Name of the data file isn't fixed, its name will be any from ABC to XYZ only fixed thing here is file extension ".Xlsx" & "sheet will be the first" always)

RPM applied from RPM (Day of the week) file.(Note: fileName "RPM (day of the week)" & Sheetname "RPM" always)

Macro will Create a column in the data file by name "RPM" and applying RPMs by matching 3 criteria
1) Channel,
2) an entry between Start time & End time,
3) Day of the week,

If any search criteria are missing it must show an error (#N/A), because for accuracy it must work with all 3 criteria.

Process:
Two files are in One folder "VBA Macro & RPM (day of the week)".
The data file will be any location. i.e it will be in the download folder or desktop or any folder

When I opened VBA macro and press a button that runs this macro asked*for the desired file and applied rpm and saved it at their location.

I hope it simplifies the process for understanding

Have you tried one in my last post?

Yes I tried, in RPM column few 19:00 hrs value is coming N/A.

2 rows (438, 490) must be "N/A".
Check the RPM sheet in row 41, Fri & Sat are blank.

Well, this macro is working well. My requirements are fulfilled.

One thing I want to request to add (if possible) is make this macro in such a way that it ask about data file and not RPM file, also it read RPM file automatically from the VBA location.

You helped a lot, I thank you from the bottom of my heart, you're a great excel genius. God Bless You, THANKS AGAIN.

Then you need to know the folder path and the file name of [RPM] workbook that I believe the name is changing.

We are not changing RPM workbook name, and macro file & RPM file will be in a same folder. And the location of that folder will be any, i.e., on desktop or any location of drive. Folder name is estimated RPMs.

I'm not getting.

File name is not changing, but location of the folder could be anywhere.
It that what you mean?

RPM file that is being used in macro will not be changed. That file is in a folder in which our macro folder is already there. That folder could be anywhere and folder name is estimated RPMs.

Eg., like you sent me a zipped folder earlier named, 'files' which has 3 files: data, RPM & VBA macro. I can place this folder at any of the location of my drive, macro is picking RPM file from that folder.

In my case, I only change this folder name from file to estimated RPMs.

OK, so all 3 books as in the same folder, makes a lot easier.

Please Login or Register  to view this content.

This code displays runtime error 1004.

Ok Jindon thanks, leave it I'll use previous final code. Thank you, you helped me a lot. May God take good care of you, stay safe & happy. Goodbye!

Originally Posted by jindon

OK, this is the mod code of my 1st code.

1) Just run the code.
2) If [Data.xlsx] is not open, the code will ask you to select [Data.xlsx] showing dialogue.
3) It will ask when Data.xlsx Sheet1 has heading named "buying RPMs", Yes/No.
4) It will then ask to select [RPM] workbook, even if it is open already.

Please Login or Register  to view this content.

Hi Jindon,
Hope you are doing great.
Sorry to bother you again.
Suddenly code has stopped working. It is giving error, "subscription out of range" at one of its coding line. Will you able to again help me out in it? I will be very grateful from the bottom of my heart.

Originally Posted by Abida Gohar

Suddenly code has stopped working. It is giving error, "subscription out of range" at one of its coding line.

No idea.
It could be found if you upload all the workbooks that you are working with.

Originally Posted by jindon

No idea.
It could be found if you upload all the workbooks that you are working with.

There are 3 files. One is data file, one is code file and one is rpm file. If you say I can attach those 3 files here? If you look at this, will be a great help for me!? ((

You can place those 3 workbooks in a folder and zip it, so that you can upload the zip file.

Trio files are attached in the zip format.
Thanks.

My original code in #52

Please Login or Register  to view this content.

the code you attached

Please Login or Register  to view this content.

None of these matched heading is in the 1st row of "Data" sheet.

My boss asked me to change file name and column name. That's why I had to change. Rest of the code is all yours. It was working 100%, just few days back it started giving error.
Sorry for all these changes, I had to listen to my boss :'(

In fact my boss was saying this code is static the column positions are fixed in this code. It should be dynamic, like it find itself the required column from multiple columns and work on it... He scolded me as well

OK, the problem is that you have many ERROR(#VALUE!) in RPM 2021 sheet.

Originally Posted by jindon

OK, the problem is that you have many ERROR(#VALUE!) in RPM 2021 sheet.

That is my boss' file. I haven't created that file.

Originally Posted by jindon

OK, the problem is that you have many ERROR(#VALUE!) in RPM 2021 sheet.

I just have checked, if you remove that error value still it is giving error at this line.

Line is this: b(i, 1) = IIf(w(2, ii - 1) = "", "N/A", w(2, ii - 1))

This will replace Error with 0 to calculate

Please Login or Register  to view this content.

Originally Posted by jindon

This will replace Error with 0 to calculate

Please Login or Register  to view this content.

Thank you. It is working You are great

Originally Posted by Abida Gohar

That is my boss' file. I haven't created that file.

ٰIsn't it possible to make it dynamic? Are there any options in VBA macro?

I don't understand what you are trying to say.

How "dynamic"?

Originally Posted by jindon

I don't understand what you are trying to say.

How "dynamic"?

Sometimes data headers are not in same order and not in first 4 columns as well. We have to re-arrange that in the form of date, time, channel and day. My boss is asking me to create such kinda code in which we don't need to re-arrange columns in a specific sequence. Unlike this one, the program just starts, finds required column (date, time, channel and day) and work thru it.

Column names will always remain same, but in data their position keeps changing.

I hope you understand now?

Tell your boss "don't ask too much".

Will review the code as I don't fully remember what it does, so maybe tomorrow.

Originally Posted by jindon

Tell your boss "don't ask too much".

Will review the code as I don't fully remember what it does, so maybe tomorrow.

Yeah, I told him, but you know, boss is boss and always demanding..

Thanks for your favour

Please Login or Register  to view this content.

Originally Posted by jindon

Please Login or Register  to view this content.

Hi Jindon, Hopefully you are well. Sorry for replying late, was occupied with some work. I checked this code yesterday it works very well. Just found a small issue.
When applying RPMs the records whose time 00:00:00, 01:00:00, 02:00:00, 03:00:00, 04:00:00, 05:00:00, ........ 23:00:00 are left blank in Est rpm column. In easy words, records of this time frame are not picking rpms. Any idea what's the issue? Hear from you soon! Thanks in advance...