Solved

vba macro which search particular text in a given column

Posted on 2013-06-07
4
442 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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.
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…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

813 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

10 Experts available now in Live!

Get 1:1 Help Now