Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

vba macro which search particular text in a given column

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
pg1533
Asked:
pg1533
  • 2
  • 2
1 Solution
 
broro183Commented:
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
 
pg1533Author Commented:
Thank you
0
 
broro183Commented:
hi pg1533,

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

Rob
0
 
pg1533Author Commented:
Sure, I will do that.

Thank you,
Prashanth
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now