Create Search in Excel for value-amount columm E

Sorry Patel i write wrong its in columm E.smile Have a look please thanks

HI Patel I cant make it work if i search in file with this 24.741,25 in box "Amount to find"
Box 2 come up "Precisions range". What shal i type there?
And
There is 2 amount with 24.741,25 in sheet 1 i would like the result to be copied to sheet 2, the complete row. so when i search for amount the found rows will be copied into sheet 2

But i dont know what to type with precicion range?

P.s i can have preciosions range in seperate macro, if this is more easy

Please have a a look at this attachment Book2.xls and see if you can correct it so 24.741,25 is copied for both rows over to sheet 2.

Thanks in advance Abjac

Thanks Patel´

It seems to be working also with the prange, so really great. But i would like to find all the rows with selected amount and prange, and copy it all in a list to sheet2
So i can search for amount with the prange, and it copy all found rows in sheet1 to sheet 2
So i have the list seperated. How to put that in to the code?

Thanks in advance

abjac

Hi Patel I have an error here. Here below is how i would like it to be. I Search in a seperate file, open it and search for "amount" and put in "Prange".After that it will copy to sheet2. And i can Save to seperate sheet if i push the save button. But it give me an error.

Please see below files and use the the testsearchfile.xls for the file to search in.
Please have a look at them and see if you can make them work

See new files below

Thanks in advance

abjac

HI Patel. I have tried to find out of it.
When i use SearchAmount.xls It shall open a selected other excelfile ( In this case i send you Testsearchfile.xls) And when i press OpenFile and Search Amount it shall open selcted excelfile(In this case Testsearchfile.xls) It shall come up with a "Amountbox" And after "P´rangebox" and after that 1. close Testsearchfile.xls, and then copy found result to sheet3 in SearchAmount.xls

I see an error in this line.

Sheets(2).Rows(drow) = Rows(k).Value

1. open file
2. search in file for amount and prange
3.copy search result to sheet 2 in
SearchAmount.xl
4. Close Testsearchfile.xls

Please Login or Register  to view this content.

Please see if you can make this code work Patel

Thanks

HI Patel. Yes i was confused what you mean. But i think i know now.
Your code look like this and it works like a charm. Thanks alot It help me alot
for all your help. Really so great Have a nice day Abjac

Working Code:

Please Login or Register  to view this content.

Hi Patel.

Wanted to ask you if i want to make a new macro for search in the date. I mean a date range.
So 1. message box come up " Input Startdate" and after a new messagebox " input Enddate"
How will this macro look? My date format now is this 06/12/2012. But could also be like this 06.12.2012. Or maybe like this 06-12-2012. So that the format is variable between these 3.
The search have to be done in columm B
Looking forward to hear you answer.

Thanks in advance

Abjac

Hi Patel.
I have now one bottom for "Searh Word Raw Bankinfo"
It find the word and paste the result to sheet 3

Code is :

Please Login or Register  to view this content.

And i have another Bottom for " search Amount"
And it copy the result to Sheet 3
Code is:

Please Login or Register  to view this content.

How can i make a tick box "Tick this to include Start and End Date" ?

And use this code you put in before in both Bottoms.

Please Login or Register  to view this content.

That will be the optimal search

If i tick it will include the Start and end Date

If not tick it will search like normal ( Both options but seperately)

Please have a look, i know this is difficult

But if you have time. It will be great

Thanks in advance.

P:S I have put a search file you can play with

But the macro is in another seperate excel, so i use this one to search in another excelfile

See my bottoms also in the attachments

Abjac

Hi Patel.
Its like this
1. Bottom . Name; " Search Word Raw Bankinfo" This open filedialog. Search word in columm F
2. Bottom . Name; "search Amount" This Open filedialog Search amount and Prange in columm B
See screen shot i send.

I want to include the "sub searchdate()"
With tickbox. So when mark the box it will also include searchdate.

Exsample.

I mark tickbox (Searchdate macro)

Then i press bottom 1.
1. It open filedialog, i choose excelfile for search.
Then it open sdate = InputBox("Input Startdate ")
and
enddate = InputBox("Input Enddate ")
2. Then it open msg box InputBox("Word to find?")
I write word.
And it copy found to sheet 3 with the startdate and enddate.

If no mark in tickbox. Just normal with no datesearch.

The same for search amount

If box is marked
It open filedialog, i choose excel file to search in
Then it open sdate = InputBox("Input Startdate ")
and
enddate = InputBox("Input Enddate ")
Then it open myWord = InputBox("Amount to find ?")
then it open InputBox("precision range ?")
It copy result to sheet 3.

If box is no marked.
It just search normal after amount and Prange.

I put the file in here. Its working try work with it so searchdate can work also with the start and end date.
Please have a look at below files.

Thanks in advance

Abjac

HI Patel I dont know if you have time to see the file i send you before. But i think i have made the solution my self.
Please try this new file i have made.
I have only one bug. When i put in the Start and End Date. If i do this in wrong format by mistake excample like this 21.12.2012
the program will debug and stop. Is there a way to fix this. Please try your self in this new file.

And also a little thing . How do i in the macro put more than one columm in for search.

Please Login or Register  to view this content.

And also a little thing . How do i in the macro put more than one columm in for search.


am = Range("E" & k) Here its only in columm E, how to put example E and D ?

Please have a look at this new file and if you can fix the little but it will be great.

Thanks in advance
Abjac

HI Patel. Thank you for the answer. Sorry i send a incorrect File.
I tested the code you send and it say there is an error in this line
Next k
Its say "Invalid Next control variablereference"

If you have time please have a look. Would be nice to be able to search in just not Columm B, but how can i put coed in to searh in more than one columm ?

Thanks

Abjac

HI Patel could not really make this work.
This one is working finally i made it. It will open op filedialog and then start and end date.
After that. it will put it in sheet 2 and from there i press to search for the examplæe amount and it all end in sheet 3. But its working. Only thing i dont know how to expand this, if i wanted to search more than in columm B . But if you know how, please tell me.

This is the code now. Working.
But only for search in columm B

Please Login or Register  to view this content.

Have a look if you have time.

And thank you very much for your help. I use these search every day in sheets with around 60000 rows, So its very important for me

Thanks

Abjac

Hi Patel Here is a test file with dates also in columm A. Try have a look if you can make it search in both columms.

Thanks
Abjac

HI Patel. Thank you very much its working. I can add sheets depending of what i want to do. But basically this code works. And can search in both columm A and B.

Thank you very much. Really great help for me.

Sincerely

Abjac

HI Patel i posted new thread "Clear with rangeclear without first row"

Could you have a look at this one, i think you can easy answer this one.

Thanks in advance

Abjac