Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace in a DDE string!!!
I would like to replace 2 sets of characters within a DDE string/object/link
(not sure of the correct term) accross a row of cells with a value entered into 2 cells at the begining of the row ie Cells C1:S1 contain DDE links to an external program from which live data is retrieved, an example of which- =the962|IQVALUE!'L12O099R(G)' A1 - value of L22 is entered by the user. B1 - value of O055 is entered by the user. What I would like to happen is when the user enters L22 into A1, L** is found in the DDE link and replaced with L22. Simalarly, when O055 is entered into B1, O*** is found and replaced with O055. The letter in brackets at the end of the link needs to remain intact. I have tried recording a macro to get me started with the code but I cant figure out how to use a cell value and wildcards as the find and replace criteria. Thanks in Advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace in a DDE string!!!
Selection.Replace What:="O???", _
Replacement:="O055", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False -- Regards, Tom Ogilvy "Mark Dullingham" wrote in message ... I would like to replace 2 sets of characters within a DDE string/object/link (not sure of the correct term) accross a row of cells with a value entered into 2 cells at the begining of the row ie Cells C1:S1 contain DDE links to an external program from which live data is retrieved, an example of which- =the962|IQVALUE!'L12O099R(G)' A1 - value of L22 is entered by the user. B1 - value of O055 is entered by the user. What I would like to happen is when the user enters L22 into A1, L** is found in the DDE link and replaced with L22. Simalarly, when O055 is entered into B1, O*** is found and replaced with O055. The letter in brackets at the end of the link needs to remain intact. I have tried recording a macro to get me started with the code but I cant figure out how to use a cell value and wildcards as the find and replace criteria. Thanks in Advance for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace in a DDE string!!!
Thanks Tom for your speedy reply.
I am a novice at VB so I appologise if I ask some seemingly obvious questions! I pasted your suggestion in to the sheet but was not sure whether it needed to be in a Sub or a function(the error it created when I pasted it came up with this) so in an attempt get something working I copied some code from another worksheet I've been working on to get a command button to pop up next to B1 when selected, to execute the replace function. the problem I've got is that I want the value in B1 to be the Replacement value. I've tried things like Target.Value and ActiveCell.Value but none work. How do I get the value in the target cell to be the replacement value? If I can get this working I plan to concantenate the first 4 cells in the row to give a text string in the format - L**O*** were the * are numbers entered, by the user, in to cells 2 and 4 in the row. One thing I may not have been clear about before is that each cell in the range selected has an individual DDE link hence why I have included a Range and Select command. Is it possible for the row in this range to be specified by the row in which a cell has been selected? ie if, as in the code below cell B1 is selected the command button pops up next to it and when pushed replaces in the range E1:Q1 then if B2 is selected then the range changes to B2:Q2. Here's the code I've got so far- Private Sub CommandButton1_Click() Range("E1:Q1").Select [I'd like the row number to change with the selected cell] Selection.Replace What:="O???", _ Replacement:=O055, _ [I'd like this to be the value in the active cell] LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("B1:B100"), Target) Is Nothing Then CommandButton1.Left = Target.Left + Target.Left CommandButton1.Top = Target.Top CommandButton1.Visible = True ' Make your Selection ElseIf CommandButton1.Visible Then CommandButton1.Visible = False End If End Sub "Tom Ogilvy" wrote: Selection.Replace What:="O???", _ Replacement:="O055", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False -- Regards, Tom Ogilvy "Mark Dullingham" wrote in message ... I would like to replace 2 sets of characters within a DDE string/object/link (not sure of the correct term) accross a row of cells with a value entered into 2 cells at the begining of the row ie Cells C1:S1 contain DDE links to an external program from which live data is retrieved, an example of which- =the962|IQVALUE!'L12O099R(G)' A1 - value of L22 is entered by the user. B1 - value of O055 is entered by the user. What I would like to happen is when the user enters L22 into A1, L** is found in the DDE link and replaced with L22. Simalarly, when O055 is entered into B1, O*** is found and replaced with O055. The letter in brackets at the end of the link needs to remain intact. I have tried recording a macro to get me started with the code but I cant figure out how to use a cell value and wildcards as the find and replace criteria. Thanks in Advance for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace in a DDE string!!!
Private Sub CommandButton1_Click() Range("E" & activecell.row & ":Q" & activecell.row).Select Selection.Replace What:="O???", _ Replacement:=Cells(activecell.row,"B").Value, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub -- Regards, Tom Ogilvy "Mark Dullingham" wrote in message ... Thanks Tom for your speedy reply. I am a novice at VB so I appologise if I ask some seemingly obvious questions! I pasted your suggestion in to the sheet but was not sure whether it needed to be in a Sub or a function(the error it created when I pasted it came up with this) so in an attempt get something working I copied some code from another worksheet I've been working on to get a command button to pop up next to B1 when selected, to execute the replace function. the problem I've got is that I want the value in B1 to be the Replacement value. I've tried things like Target.Value and ActiveCell.Value but none work. How do I get the value in the target cell to be the replacement value? If I can get this working I plan to concantenate the first 4 cells in the row to give a text string in the format - L**O*** were the * are numbers entered, by the user, in to cells 2 and 4 in the row. One thing I may not have been clear about before is that each cell in the range selected has an individual DDE link hence why I have included a Range and Select command. Is it possible for the row in this range to be specified by the row in which a cell has been selected? ie if, as in the code below cell B1 is selected the command button pops up next to it and when pushed replaces in the range E1:Q1 then if B2 is selected then the range changes to B2:Q2. Here's the code I've got so far- Private Sub CommandButton1_Click() Range("E1:Q1").Select [I'd like the row number to change with the selected cell] Selection.Replace What:="O???", _ Replacement:=O055, _ [I'd like this to be the value in the active cell] LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("B1:B100"), Target) Is Nothing Then CommandButton1.Left = Target.Left + Target.Left CommandButton1.Top = Target.Top CommandButton1.Visible = True ' Make your Selection ElseIf CommandButton1.Visible Then CommandButton1.Visible = False End If End Sub "Tom Ogilvy" wrote: Selection.Replace What:="O???", _ Replacement:="O055", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False -- Regards, Tom Ogilvy "Mark Dullingham" wrote in message ... I would like to replace 2 sets of characters within a DDE string/object/link (not sure of the correct term) accross a row of cells with a value entered into 2 cells at the begining of the row ie Cells C1:S1 contain DDE links to an external program from which live data is retrieved, an example of which- =the962|IQVALUE!'L12O099R(G)' A1 - value of L22 is entered by the user. B1 - value of O055 is entered by the user. What I would like to happen is when the user enters L22 into A1, L** is found in the DDE link and replaced with L22. Simalarly, when O055 is entered into B1, O*** is found and replaced with O055. The letter in brackets at the end of the link needs to remain intact. I have tried recording a macro to get me started with the code but I cant figure out how to use a cell value and wildcards as the find and replace criteria. Thanks in Advance for any help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace in a DDE string!!!
Thanks Tom that worked a treat.
I've managed to concantenate the first 4 cells in to cell 5 and use that as the replacement value, so all is going well. I just need to figure out how to run the sub when the user hits the enter key whilst cell 4 in the row is selected. Thanks again for your assistance. "Tom Ogilvy" wrote: Private Sub CommandButton1_Click() Range("E" & activecell.row & ":Q" & activecell.row).Select Selection.Replace What:="O???", _ Replacement:=Cells(activecell.row,"B").Value, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub -- Regards, Tom Ogilvy "Mark Dullingham" wrote in message ... Thanks Tom for your speedy reply. I am a novice at VB so I appologise if I ask some seemingly obvious questions! I pasted your suggestion in to the sheet but was not sure whether it needed to be in a Sub or a function(the error it created when I pasted it came up with this) so in an attempt get something working I copied some code from another worksheet I've been working on to get a command button to pop up next to B1 when selected, to execute the replace function. the problem I've got is that I want the value in B1 to be the Replacement value. I've tried things like Target.Value and ActiveCell.Value but none work. How do I get the value in the target cell to be the replacement value? If I can get this working I plan to concantenate the first 4 cells in the row to give a text string in the format - L**O*** were the * are numbers entered, by the user, in to cells 2 and 4 in the row. One thing I may not have been clear about before is that each cell in the range selected has an individual DDE link hence why I have included a Range and Select command. Is it possible for the row in this range to be specified by the row in which a cell has been selected? ie if, as in the code below cell B1 is selected the command button pops up next to it and when pushed replaces in the range E1:Q1 then if B2 is selected then the range changes to B2:Q2. Here's the code I've got so far- Private Sub CommandButton1_Click() Range("E1:Q1").Select [I'd like the row number to change with the selected cell] Selection.Replace What:="O???", _ Replacement:=O055, _ [I'd like this to be the value in the active cell] LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("B1:B100"), Target) Is Nothing Then CommandButton1.Left = Target.Left + Target.Left CommandButton1.Top = Target.Top CommandButton1.Visible = True ' Make your Selection ElseIf CommandButton1.Visible Then CommandButton1.Visible = False End If End Sub "Tom Ogilvy" wrote: Selection.Replace What:="O???", _ Replacement:="O055", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False -- Regards, Tom Ogilvy "Mark Dullingham" wrote in message ... I would like to replace 2 sets of characters within a DDE string/object/link (not sure of the correct term) accross a row of cells with a value entered into 2 cells at the begining of the row ie Cells C1:S1 contain DDE links to an external program from which live data is retrieved, an example of which- =the962|IQVALUE!'L12O099R(G)' A1 - value of L22 is entered by the user. B1 - value of O055 is entered by the user. What I would like to happen is when the user enters L22 into A1, L** is found in the DDE link and replaced with L22. Simalarly, when O055 is entered into B1, O*** is found and replaced with O055. The letter in brackets at the end of the link needs to remain intact. I have tried recording a macro to get me started with the code but I cant figure out how to use a cell value and wildcards as the find and replace criteria. Thanks in Advance for any help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and Replace in a DDE string!!!
Tom
Just thought I'd let you know that with your help I have managed to acieve what I wanted originally. The code I've used is as follows- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("B1:B126", "D1:D126"), Target) Is Nothing Then Range("G" & ActiveCell.Offset(-1, 0).Row & ":S" & ActiveCell.Offset(-1, 0).Row).Select Selection.Replace What:="L??O???", _ Replacement:=Cells(ActiveCell.Row, "E").Value, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End If End Sub I'm sure there is an easier way of doing this but hey it works. Thanks again for your help with this. "Mark Dullingham" wrote: Thanks Tom that worked a treat. I've managed to concantenate the first 4 cells in to cell 5 and use that as the replacement value, so all is going well. I just need to figure out how to run the sub when the user hits the enter key whilst cell 4 in the row is selected. Thanks again for your assistance. "Tom Ogilvy" wrote: Private Sub CommandButton1_Click() Range("E" & activecell.row & ":Q" & activecell.row).Select Selection.Replace What:="O???", _ Replacement:=Cells(activecell.row,"B").Value, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub -- Regards, Tom Ogilvy "Mark Dullingham" wrote in message ... Thanks Tom for your speedy reply. I am a novice at VB so I appologise if I ask some seemingly obvious questions! I pasted your suggestion in to the sheet but was not sure whether it needed to be in a Sub or a function(the error it created when I pasted it came up with this) so in an attempt get something working I copied some code from another worksheet I've been working on to get a command button to pop up next to B1 when selected, to execute the replace function. the problem I've got is that I want the value in B1 to be the Replacement value. I've tried things like Target.Value and ActiveCell.Value but none work. How do I get the value in the target cell to be the replacement value? If I can get this working I plan to concantenate the first 4 cells in the row to give a text string in the format - L**O*** were the * are numbers entered, by the user, in to cells 2 and 4 in the row. One thing I may not have been clear about before is that each cell in the range selected has an individual DDE link hence why I have included a Range and Select command. Is it possible for the row in this range to be specified by the row in which a cell has been selected? ie if, as in the code below cell B1 is selected the command button pops up next to it and when pushed replaces in the range E1:Q1 then if B2 is selected then the range changes to B2:Q2. Here's the code I've got so far- Private Sub CommandButton1_Click() Range("E1:Q1").Select [I'd like the row number to change with the selected cell] Selection.Replace What:="O???", _ Replacement:=O055, _ [I'd like this to be the value in the active cell] LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Application.Intersect(Range("B1:B100"), Target) Is Nothing Then CommandButton1.Left = Target.Left + Target.Left CommandButton1.Top = Target.Top CommandButton1.Visible = True ' Make your Selection ElseIf CommandButton1.Visible Then CommandButton1.Visible = False End If End Sub "Tom Ogilvy" wrote: Selection.Replace What:="O???", _ Replacement:="O055", _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ MatchCase:=False -- Regards, Tom Ogilvy "Mark Dullingham" wrote in message ... I would like to replace 2 sets of characters within a DDE string/object/link (not sure of the correct term) accross a row of cells with a value entered into 2 cells at the begining of the row ie Cells C1:S1 contain DDE links to an external program from which live data is retrieved, an example of which- =the962|IQVALUE!'L12O099R(G)' A1 - value of L22 is entered by the user. B1 - value of O055 is entered by the user. What I would like to happen is when the user enters L22 into A1, L** is found in the DDE link and replaced with L22. Simalarly, when O055 is entered into B1, O*** is found and replaced with O055. The letter in brackets at the end of the link needs to remain intact. I have tried recording a macro to get me started with the code but I cant figure out how to use a cell value and wildcards as the find and replace criteria. Thanks in Advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace a string contained in a link | Excel Discussion (Misc queries) | |||
Find and replace part of a text string | Excel Discussion (Misc queries) | |||
Excel - Find & Replace text in a string | Excel Programming | |||
Excel - Find & Replace text in a string | Excel Worksheet Functions | |||
Find/Replace String | Excel Programming |