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.
> > > >
> > > >
> > > >
> >
> >
> >