Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vba macro which search particular text in a given column

Posted on 2013-06-07
4
Medium Priority
?
462 Views
Last Modified: 2013-06-25
Hi,

I have a workbook which has the following sheets.

Search
Recommendations - Raw data
Findings - Raw data
Findings – Result
Recommendations – Result
I am trying to search particular text in the following worksheets

‘Recommendations - Raw data’, column ‘F’ and
‘Findings - Raw data’, column ‘F’

I am entering which text should be searched in text box in ‘Search’ spreadsheet. Once I click on search button then the macro should search in the above mentioned columns only. If the text matches then the copy of that entire data should be retrieved from that column and placed in ‘Recommendations – Result’ worksheet if the text is found in ‘Recommendations - Raw data’ OR should be retrieved from that column and placed in ‘Findings – Result’ worksheet if the text is found in ‘Findings - Raw data’. I am sure we can use vlookup but if a macro does this then it would be really helpful.  Please advise.

I did try this macro with myworkbook. But it is not working. Please note that in the given macro file names and referenced controls are different.

Sub Summ()

Workbooks.Open Filename:="Summary.xlsx"
Windows.Arrange ArrangeStyle:=xlVertical
Windows("RESULT.xlsm").Activate
Cells.Find(What:="Air System Name", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
Range("B10").Select
Selection.Copy
Windows("Summary.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("RESULT.xlsm").Activate
Cells.Find(What:="Floor Area", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
Range("B14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Summary.xlsx").Activate
Range("B5").Select
ActiveSheet.Paste
Windows("RESULT.xlsm").Activate
Cells.Find(What:="Total coil load", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
Range("B27").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Summary.xlsx").Activate
Range("C5").Select
ActiveSheet.Paste
Windows("RESULT.xlsm").Activate
Cells.Find(What:="Sensible coil load", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range("B28").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Summary.xlsx").Activate
Range("D5").Select
ActiveSheet.Paste
Windows("RESULT.xlsm").Activate
Cells.Find(What:="Max block L/s", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
Range("B30").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Summary.xlsx").Activate
Range("E5").Select
ActiveSheet.Paste
Range("A6").Select

End Sub

Open in new window

For-Search-by-Text-option.xlsm
0
Comment
Question by:pg1533
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
broro183 earned 2000 total points
ID: 39228565
hi pg1533,

Does the example code in the file attached to this post help?

Or do any of the other responses in that question help out?

If it looks promising, have a go at modifying it & let us know if you have any problems.

hth
Rob
0
 

Author Closing Comment

by:pg1533
ID: 39274081
Thank you
0
 
LVL 10

Expert Comment

by:broro183
ID: 39274093
hi pg1533,

Thank you for the points - feel free to ask any questions if you need help modifying any of the code.

Rob
0
 

Author Comment

by:pg1533
ID: 39274101
Sure, I will do that.

Thank you,
Prashanth
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question