Erasing Set of Character in a row.

[saikumar]

Hi All,

I'm looking to create a macro that will take a input from extract master.xls workbook and search that contain in the transaction.xls workbook.

I need not want to search the entire row exactly, instead if the search string found any where in that row, that search string alone need to be removed from that row.

For example.

Search String (File : extract master.xls, Column : TAG) :-

PVT LTD

Search Row (File : transaction.xls, Column : INVESTOR'S NAME) :-

GATIMAN AUTO PVT LTD
RGK CONTROLS & COMPONENTS PVT LTD EMPGRO UP GRATUITY FUND
RAVI SERVICES PVT LTDSTAFF PROVIDENT FUND

Output Row :-

GATIMAN AUTO
RGK CONTROLS & COMPONENTS EMPGRO UP GRATUITY FUND
RAVI SERVICES PVT LTDSTAFF PROVIDENT FUND

The output row need to write at next column. I need to copy all the row to next column. The changed row need to filled with coloured, remaining other row will be copied exactly like that.

Examples I've found on this forum, I still cannot crack it and it doesn't solve my problem. Any help is greatly appreciated. Thanks.

[saikumar]

code

Please Login or Register  to view this content.

I have almost completed with my work, still i need to fill the colour for "NEW INVESTOR'S NAME", I don't now how to fill the colour ?

I have highlighted with red colour in the above code ??? which doesn't work for me.


thanks.

[broro183]

hi Saikumar,

In the future please try to keep the same topic to a single thread or to at least link the 2 threads as stated in the rule # 8.
Rules:
http://www.excelforum.com/forum-rule...rum-rules.html
Cross posted as:
http://www.excelforum.com/excel-prog...new-sheet.html

Anyway...
I think you have done a much better job of explaining your problem in this thread :-)

& I've done soe work on it so, I'm not sure at a glance why your line of code doesn't work...?
Does the attached file do what you want?
(of course you may want to change the interior colour from Gold to something else)

Note, I've also changed the technique from a looping check to a search & replace of the range which will hopefully be faster.

hth
Rob

[saikumar]

hi Saikumar,

In the future please try to keep the same topic to a single thread or to at least link the 2 threads as stated in the rule # 8.
Rules:
http://www.excelforum.com/forum-rule...rum-rules.html
Cross posted as:
http://www.excelforum.com/excel-prog...new-sheet.html

Anyway...
I think you have done a much better job of explaining your problem in this thread :-)

& I've done soe work on it so, I'm not sure at a glance why your line of code doesn't work...?
Does the attached file do what you want?
(of course you may want to change the interior colour from Gold to something else)

Note, I've also changed the technique from a looping check to a search & replace of the range which will hopefully be faster.

hth
Rob

Thanks for your replay. I will correct my error in future. I will check the result and post this thread solved. Thanks a lot.

Regards,
Sai Kumar

[saikumar]

hi Saikumar,

In the future please try to keep the same topic to a single thread or to at least link the 2 threads as stated in the rule # 8.
Rules:
http://www.excelforum.com/forum-rule...rum-rules.html
Cross posted as:
http://www.excelforum.com/excel-prog...new-sheet.html

Anyway...
I think you have done a much better job of explaining your problem in this thread :-)

& I've done soe work on it so, I'm not sure at a glance why your line of code doesn't work...?
Does the attached file do what you want?
(of course you may want to change the interior colour from Gold to something else)

Note, I've also changed the technique from a looping check to a search & replace of the range which will hopefully be faster.

hth
Rob

Excellent work. No word to say great stuff. It works as i want. Thanks.

In my first post I need to do 2 jobs,since putting 2 things together which makes you ppl to difficult to understand. The 2 jobs are :-

1. Find & Erase
2. Sorting 3 column and Find/Selected the repeated row.

I thought of spliting this 2 job in different post and explaining to you ppl. The first job is get completed iin this thread. Need to work on 2nd job, the details will be there in my first post you mention link below.

http://www.excelforum.com/excel-prog...new-sheet.html

I think i have not done against the rule.

thanks.
Sai kumar

[saikumar]

code:

Please Login or Register  to view this content.

[broro183]

Thanks for the feedback - I'm pleased I could help. "One down, one to go" :-)

Rob