Solved

vba macro which search particular text in a given column

Posted on 2013-06-07
4
436 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
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
broro183 earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now