Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste to Find dialog box using a macro
Manually I can paste a value into the Find what: field of the find dialog
box (Ctrl+F, Ctrl+V). However, when I attempt to record this into a macro, the macro will not record it. Am I missing something? Thanks, for any help you can offer. Dan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste to Find dialog box using a macro
Option Explicit
Sub testme() Dim MyData As DataObject Dim myStr As String Set MyData = New DataObject myStr = "" 'or whatever you want if there's no text in the clipboard On Error Resume Next MyData.GetFromClipboard myStr = MyData.GetText(1) On Error GoTo 0 'maybe this, too??? myStr = Replace(myStr, vbCrLf, " ") myStr = Replace(myStr, vbCr, " ") myStr = Replace(myStr, vbLf, " ") Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr End Sub Chip Pearson has some notes that you'll want to read. http://www.cpearson.com/excel/clipboard.htm Especially the note about using tools|references and checking "Microsoft Forms 2.0 object library"). danno-c wrote: Manually I can paste a value into the Find what: field of the find dialog box (Ctrl+F, Ctrl+V). However, when I attempt to record this into a macro, the macro will not record it. Am I missing something? Thanks, for any help you can offer. Dan -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste to Find dialog box using a macro
I got a compile error from the Dim MyData As DataObject line. I'm looking
through the help files now to see what i can do. I'm sure this is just a newbie problem on my end. "Dave Peterson" wrote: Option Explicit Sub testme() Dim MyData As DataObject Dim myStr As String Set MyData = New DataObject myStr = "" 'or whatever you want if there's no text in the clipboard On Error Resume Next MyData.GetFromClipboard myStr = MyData.GetText(1) On Error GoTo 0 'maybe this, too??? myStr = Replace(myStr, vbCrLf, " ") myStr = Replace(myStr, vbCr, " ") myStr = Replace(myStr, vbLf, " ") Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr End Sub Chip Pearson has some notes that you'll want to read. http://www.cpearson.com/excel/clipboard.htm Especially the note about using tools|references and checking "Microsoft Forms 2.0 object library"). danno-c wrote: Manually I can paste a value into the âœFind what:â field of the find dialog box (Ctrl+F, Ctrl+V). However, when I attempt to record this into a macro, the macro will not record it. Am I missing something? Thanks, for any help you can offer. Dan -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste to Find dialog box using a macro
Found it.
It's all good now. Great link. Thanks, Dan "danno-c" wrote: I got a compile error from the Dim MyData As DataObject line. I'm looking through the help files now to see what i can do. I'm sure this is just a newbie problem on my end. "Dave Peterson" wrote: Option Explicit Sub testme() Dim MyData As DataObject Dim myStr As String Set MyData = New DataObject myStr = "" 'or whatever you want if there's no text in the clipboard On Error Resume Next MyData.GetFromClipboard myStr = MyData.GetText(1) On Error GoTo 0 'maybe this, too??? myStr = Replace(myStr, vbCrLf, " ") myStr = Replace(myStr, vbCr, " ") myStr = Replace(myStr, vbLf, " ") Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr End Sub Chip Pearson has some notes that you'll want to read. http://www.cpearson.com/excel/clipboard.htm Especially the note about using tools|references and checking "Microsoft Forms 2.0 object library"). danno-c wrote: Manually I can paste a value into the âœFind what:â field of the find dialog box (Ctrl+F, Ctrl+V). However, when I attempt to record this into a macro, the macro will not record it. Am I missing something? Thanks, for any help you can offer. Dan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I write a macro to show the find dialog box in excel 2003 | Excel Discussion (Misc queries) | |||
Macro to find and paste rows | Excel Discussion (Misc queries) | |||
Macro to Find, Cut, and Paste | Excel Discussion (Misc queries) | |||
I need a macro to find cut and paste data to new cell | Excel Discussion (Misc queries) | |||
I need a macro to find cut and paste data to new cell | Excel Discussion (Misc queries) |