Selecting a Keyword for Macro:

[Gmata]

Hello Guys thanks for all the previous help you had offer to me before, i am
in the last step of completing this project and i think is very simple but i
need some help from the experts.

How can i make the Macro to "Find" a specific phrase "always the same Phrase"

and then copy For example 300 cells under that specific phrase.

Thanks

 

[Per Jessen]

Hi

Look at this:

Sub MyFind()
Dim SearchRng As Range

Set SearchRng = Range("A1:H2")
Set f = SearchRng.Find(what:="Phrase", after:="A1", Lookat:=xlWhole)

If Not f Is Nothing Then
f.Offset(1, 0).Resize(300, 1).Copy
End If
End Sub

Regards,
Per

 

[Jacob Skaria]

Sub Macro1()
Dim varFind As Range
Set varFind = Cells.Find(What:="keyword")
If Not varFind Is Nothing Then
varFind.Offset(1, 0).Resize(300, 1).Copy
'do what ever

End If
End Sub

If this post helps click Yes

 

[Gmata]

Is there any way to do it with out actually writing the code, but i just want
to record a Macro, so I just need to know what to press to always go to the
block where that phrase is, because that phrase might be on A100 or A300, so
if i Just click Find and then i typed the phrase the macro gets recorded as
going to a certain cell rather than going to where that Phrase is actually at.

Thans

 

[Jacob Skaria]

When you record a macro; MS Excel will automatically generate the code. To
record a macro goto menu

For XL2003 Tools>Macro>REcord New Macro>OK.

For XL2007: Commands for working with macros and XML are on the Developer
tab, which is displayed only if your turn it on. To display the Developer tab
do the following.
1. Click the Microsoft Office Button, and then click Excel Options
2. Click Popular, and then click Show Developer tab in the Ribbon

As per your requirement mentioned below; the below code will ask for the
search string and if found copy 300 cells down..If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro2()
Dim strSearchString As String, varFind As Range
strSearchString = InputBox("Search String")
Set varFind = Cells.Find(What:=strSearchString)
If Not varFind Is Nothing Then _
varFind.Offset(1, 0).Resize(300, 1).Copy
End Sub


If this post helps click Yes