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
pg1533Asked:
Who is Participating?
 
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.

All Courses

From novice to tech pro — start learning today.