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.