To find Row with certain value and to check if range "M" of that Row has certain value.

**dilshod_k** · 01-01-2021, 12:58 PM

Hello everyone,

I have Sub that loops through multiple files and the aim is to find row in range "B:B" (column B with Dates in descending order) with certain Date (date I'm looking for is recorded in range "W1" of Sheet1 in thisworkbook) and to check the value of range "M" of that row. If the value of the range "M" is "BUY" then I want to copy Range "A:O" of that row and Paste it to MasterSheet of thisworkbook, otherwise to close the file and proceed to the next one. I've figured out how to find cell with value, but I do not know how to incorporate "If" condition to check range "M" value of that row. I would be grateful for any help. Of course it does not have to be Find function if it is not the best way to solve problem, but one thing is important is that I need to be able to search date which is indicated in range "W1" and that value I want to be able to change manually whenever needed. Thank you in advance for your help.
Dilshod

Please Login or Register  to view this content.

**AlphaFrog** · 01-01-2021, 01:59 PM

Please Login or Register  to view this content.

**dilshod_k** · 01-01-2021, 04:18 PM

Hi AlphaFrog,
My apologies but the code is not working. I’m not sure why, may be Find portion of my code doesn’t do its job. I was wandering if you could have a look at the workbook and test file and figure out problem? Thank you in advance for your time.

P.S. Failed to attach files as when I click "GoAdvanced" I'm not getting option "Manage Attachments".

Please Login or Register  to view this content.

**AlphaFrog** · 01-01-2021, 10:41 PM

What does "not working " exactly mean? Do you get an error? If yes, what is the error description and is a line of code highlighted when you Debug? Does it find a date match, but not copy the values?

Do the dates in column B and the search-for date have the same date format?

This is a guess, but give it a try...

Set sDate = wb.Range("B:B").Find(what:=destwb.Worksheets("Sheet1").Range("B1").Formula, LookIn:=xlFormulass, LookAt:=xlWhole)

**dilshod_k** · 01-02-2021, 06:20 AM

There is no error messages, it works smooth, that is why I thought that Find portion of the code is working, but when I've tried to run it with Copy/Paste part id did not copied/pasted searchable range.
It does not find date.
I've tried to change search arguments it did not help. Search-for date and Column B:B Dates formats look the same : 11/6/2020 14:30
I've taken screenshots of both of the files but cannot attach them as attachment button does not work in my account.
when I tried to change code to sDate.Activate or sDate.Select it does not selects cell with date value, I tried to change from xlWhole to xlPart, it did not change anything.

**dilshod_k** · 01-02-2021, 06:50 AM

When I try to debug, when the line Set sDate = wb.Range("B:B").Find(what:=destwb.Worksheets("Sheet1").Range("B1").Value, LookIn:=xlValues, lookat:=xlWhole) is highlighted and I hover cursor over sDate it gives sDate = Nothing.

**sintek** · 01-02-2021, 07:03 AM

difficult without file upload...
If you are making use of this from post 4 then remove red snippet...

Please Login or Register  to view this content.

**mohadin** · 01-02-2021, 07:16 AM

Perhaps

Please Login or Register  to view this content.

**dilshod_k** · 01-02-2021, 07:17 AM

Originally Posted by sintek

difficult without file upload...
...

Thanks for your message.
I do not know why "Attachments" button is not working in my case, I've tried several browsers with no result. I might have to look for help at another forum.
Changing xlFormulass to xlFormulas did not change result.

**dilshod_k** · 01-02-2021, 07:24 AM

Originally Posted by mohadin

Perhaps

Please Login or Register  to view this content.

Thanks for your message.
I've tried to change line to:
Set sDate = wb.Worksheets(1).Range("B:B").Find(what:=destwb.Worksheets("Sheet1").Range("B1").Value, LookIn:=xlValues, lookat:=xlWhole)
it did not help.
name of the single sheet in each wb is the same as wb name.

**sintek** · 01-02-2021, 08:11 AM

dilshod_k...
No need to quote entire posts...it just clutters the thread...
To upload sample file...
Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
Can go around in circles without it...You are looking for the text "Date"...should not be that complicated unless it does not exist in Column B

**dilshod_k** · 01-02-2021, 08:39 AM

Hi sintek,
when I click "Go Advanced" I do not get Tab or Button "Manage Attachments", I just get two tabs "Submit Reply" and "Preview Post", but I do get new button with paper clip ("Attachments") amidst text formatting options of the message. This button is not working on my page regardless of browser I'm using. I would record video of that and send it to you to prove but I'd still not be able to attach it.

**dilshod_k** · 01-02-2021, 08:51 AM

I think I found Manage attachments link. I've uploaded them and the only option remains is to close window. When I close it I do not see any files attached to message. Only when I press preview I can see them.

**dilshod_k** · 01-02-2021, 08:59 AM

As for some reasons I was not able to attach files to my posts, I've cross published same question on another forum earlier today but I was not able to post link of that forum as my account apparently has restrictions on posting links.

**dilshod_k** · 01-02-2021, 09:10 AM

"You are looking for the text "Date"...should not be that complicated unless it does not exist in Column B" -

I'm not looking for text "Date". I'm looking for value in Date format (11/6/2020 14:30).

**AliGW** · 01-02-2021, 09:13 AM

I've cross published same question on another forum earlier today but I was not able to post link of that forum as my account apparently has restrictions on posting links.

You can post links now that you have posted here 10 times.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

**dilshod_k** · 01-02-2021, 09:17 AM

Thank you!
The same query has been posted on:

https://www.ozgrid.com/forum/index.p...y-paste-range/

**sintek** · 01-02-2021, 10:11 AM

This works for me...Change red snippet to your path...

Please Login or Register  to view this content.

**jindon** · 01-02-2021, 10:27 AM

If the csv files are all identically structured then

Please Login or Register  to view this content.

**dilshod_k** · 01-02-2021, 10:38 AM

Hi sintek,

Works perfectly well. Impressive. Thanks a lot. I would not be able to sort it out on my own. Appreciate it!

**AliGW** · 01-02-2021, 10:39 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**sintek** · 01-02-2021, 10:53 AM

Originally Posted by dilshod_k

Hi sintek,

Works perfectly well. Impressive. Thanks a lot. I would not be able to sort it out on my own. Appreciate it!

Glad I could contribute...Tx for rep +

**dilshod_k** · 01-02-2021, 10:58 AM

Hi jindon-San,

Thank you very much for your help! Even though "Open Folder" dialog box is not exactly what I wanted, as I need to loop through files with folder path indicated in the code, and I needed to check only one date, I'm still grateful for your post as you gave me an idea and ready made code to retrieve all dates from the history of the stock with "BUY" order.
Arigatou gozaimashita

**jindon** · 01-02-2021, 11:51 AM

Missed the Date in B1

Please Login or Register  to view this content.

**dilshod_k** · 01-02-2021, 12:40 PM

"Missed the Date in B1"

Hi Jindon-San,

Your code works perfectly well AND it is the FASTEST CODE in terms of performance I had so far.
Normally to loop through 800 csv files it would take me something between 167 to 210 sec.
Your code does the same job in 151 sec. Thank you very much! Appreciate it.

Dilshod

**jindon** · 01-02-2021, 12:43 PM

Glad you like it and thanks for the rep.

To find Row with certain value and to check if range "M" of that Row has certain value.

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Re: To find Row with certain value and to check if range "M" of that Row has certain value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Check range and if 1 line has "Yes" all lines of the same get "Yes"

[SOLVED] Find "X" in range of cells and return various values based on the column "X" is found in.

[SOLVED] create links to check boxes marked "good" fair"and "bad"

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions