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.

Selection.Replace What:="O???", _
Replacement:="O055", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

--
Regards,
Tom Ogilvy




"Mark Dullingham" <MarkDullingham@discussions.microsoft.com> wrote in
message news:AAE8D4BE-C998-4371-986B-99AC58D1F42B@microsoft.com...
> 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.

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" <MarkDullingham@discussions.microsoft.com> wrote in
> message news:AAE8D4BE-C998-4371-986B-99AC58D1F42B@microsoft.com...
> > 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.
>
>
>

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" <MarkDullingham@discussions.microsoft.com> wrote in
message news:DD260F87-8E19-4A78-9852-1CB9F7A6595B@microsoft.com...
> 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" <MarkDullingham@discussions.microsoft.com> wrote in
> > message news:AAE8D4BE-C998-4371-986B-99AC58D1F42B@microsoft.com...
> > > 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.
> >
> >
> >

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" <MarkDullingham@discussions.microsoft.com> wrote in
> message news:DD260F87-8E19-4A78-9852-1CB9F7A6595B@microsoft.com...
> > 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" <MarkDullingham@discussions.microsoft.com> wrote in
> > > message news:AAE8D4BE-C998-4371-986B-99AC58D1F42B@microsoft.com...
> > > > 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.
> > >
> > >
> > >
>
>
>

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" <MarkDullingham@discussions.microsoft.com> wrote in
> > message news:DD260F87-8E19-4A78-9852-1CB9F7A6595B@microsoft.com...
> > > 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" <MarkDullingham@discussions.microsoft.com> wrote in
> > > > message news:AAE8D4BE-C998-4371-986B-99AC58D1F42B@microsoft.com...
> > > > > 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.
> > > >
> > > >
> > > >
> >
> >
> >