|
Insert "Find dialog box" edits into macro
I'm using a macro calls the find dialog and pastes a value into the 'find
what:' field. The problem I am having is there is a null space at the end of the pasted string (can't find string with the extra space) and I have to manually hit End and Backspace then enter. Can anyone suggest some code that I can insert into the macro to automate these keystrokes? Code below. Thanks, Dan Sub testme() ' code to open find dialog and paste clipboard contents as MyStr Dim MyData As DataObject Dim myStr As String Set MyData = New DataObject myStr = "" On Error Resume Next MyData.GetFromClipboard myStr = MyData.GetText(1) On Error GoTo 0 Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr Selection.Copy Sheets("Trip Detail").Select Application.Goto Reference:="R1C1" ActiveSheet.Paste ActiveSheet.Paste ActiveSheet.Paste ActiveSheet.Paste ActiveCell.Offset(0, -8).Range("A1:I1").Select ActiveCell.Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Range("A1").Select Sheets("Trip Listing").Select Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr End Sub |
Insert "Find dialog box" edits into macro
Have you tried trimming the string?
Application.Dialogs(xlDialogFormulaFind).Show arg1:=trim(myStr) -- HTH... Jim Thomlinson "danno-c" wrote: I'm using a macro calls the find dialog and pastes a value into the 'find what:' field. The problem I am having is there is a null space at the end of the pasted string (can't find string with the extra space) and I have to manually hit End and Backspace then enter. Can anyone suggest some code that I can insert into the macro to automate these keystrokes? Code below. Thanks, Dan Sub testme() ' code to open find dialog and paste clipboard contents as MyStr Dim MyData As DataObject Dim myStr As String Set MyData = New DataObject myStr = "" On Error Resume Next MyData.GetFromClipboard myStr = MyData.GetText(1) On Error GoTo 0 Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr Selection.Copy Sheets("Trip Detail").Select Application.Goto Reference:="R1C1" ActiveSheet.Paste ActiveSheet.Paste ActiveSheet.Paste ActiveSheet.Paste ActiveCell.Offset(0, -8).Range("A1:I1").Select ActiveCell.Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Range("A1").Select Sheets("Trip Listing").Select Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr End Sub |
Insert "Find dialog box" edits into macro
Jim,
I just tried it (also RTrim) and it had no affect on the dialog box text string. Dan "Jim Thomlinson" wrote: Have you tried trimming the string? Application.Dialogs(xlDialogFormulaFind).Show arg1:=trim(myStr) -- HTH... Jim Thomlinson "danno-c" wrote: I'm using a macro calls the find dialog and pastes a value into the 'find what:' field. The problem I am having is there is a null space at the end of the pasted string (can't find string with the extra space) and I have to manually hit End and Backspace then enter. Can anyone suggest some code that I can insert into the macro to automate these keystrokes? Code below. Thanks, Dan Sub testme() ' code to open find dialog and paste clipboard contents as MyStr Dim MyData As DataObject Dim myStr As String Set MyData = New DataObject myStr = "" On Error Resume Next MyData.GetFromClipboard myStr = MyData.GetText(1) On Error GoTo 0 Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr Selection.Copy Sheets("Trip Detail").Select Application.Goto Reference:="R1C1" ActiveSheet.Paste ActiveSheet.Paste ActiveSheet.Paste ActiveSheet.Paste ActiveCell.Offset(0, -8).Range("A1:I1").Select ActiveCell.Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Range("A1").Select Sheets("Trip Listing").Select Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr End Sub |
Insert "Find dialog box" edits into macro
Jim, my bad ... I have it working that you very much
Dan "danno-c" wrote: Jim, I just tried it (also RTrim) and it had no affect on the dialog box text string. Dan "Jim Thomlinson" wrote: Have you tried trimming the string? Application.Dialogs(xlDialogFormulaFind).Show arg1:=trim(myStr) -- HTH... Jim Thomlinson "danno-c" wrote: I'm using a macro calls the find dialog and pastes a value into the 'find what:' field. The problem I am having is there is a null space at the end of the pasted string (can't find string with the extra space) and I have to manually hit End and Backspace then enter. Can anyone suggest some code that I can insert into the macro to automate these keystrokes? Code below. Thanks, Dan Sub testme() ' code to open find dialog and paste clipboard contents as MyStr Dim MyData As DataObject Dim myStr As String Set MyData = New DataObject myStr = "" On Error Resume Next MyData.GetFromClipboard myStr = MyData.GetText(1) On Error GoTo 0 Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr Selection.Copy Sheets("Trip Detail").Select Application.Goto Reference:="R1C1" ActiveSheet.Paste ActiveSheet.Paste ActiveSheet.Paste ActiveSheet.Paste ActiveCell.Offset(0, -8).Range("A1:I1").Select ActiveCell.Activate Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste ActiveCell.Offset(1, 0).Range("A1").Select Sheets("Trip Listing").Select Application.Dialogs(xlDialogFormulaFind).Show arg1:=myStr End Sub |
| All times are GMT +1. The time now is 11:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com