Troublesome Code

[Guest]

I have the following code in my work book. It does exactly what I want it to
apart from each time a value is changed and the code runs the code scrolls
back through the entire sheet and as the number of rows is getting quite
extensive now this can take some time.
Does anyone have any idea how to stop this from happening.

Many thanks in advance

Mark Dullingham

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("b1:b150", "d1:d150"), Target) Is
Nothing Then
Range("D" & ActiveCell.Offset(-1, 0).Row & ":X" & ActiveCell.Offset(-1,
0).Row).Select
Selection.Replace what:="L??O???", _
Replacement:=Cells(ActiveCell.Row, "a").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
Target.Offset(1, 0).Select
End If


End Sub

 

[Dave Peterson]

First,

Range("b1:b150", "d1150")
is the same as:
range("B1150")

If you really wanted just rows 1:150 in column B or D, then use:
range("b1:b150,d1:d150")



Private Sub Worksheet_Change(ByVal Target As Range)
dim myRow as long
dim myRng as range

If Target.Cells.Count > 1 Then Exit Sub
myrow = target.row -1

If Application.Intersect(Range("b1:d150"), Target) Is Nothing Then
exit sub
end if

set myrng = me.range(.cells(myrow,"D"),.cells(myrow,"X"))

'stop the change from firing this event
application.enableevents = false
myrng.Replace what:="L??O???", _
Replacement:=me.Cells(target.row, "a").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

Target.Offset(1, 0).Select
application.enableevents =true

End Sub

(Untested, uncompiled.)

 

[Guest]

Thanks Dave for your reply and code,
It didn't work at first, so I had a dig aroud in the VB help files and with
a bit of trial and error (mostly error!) managed to come up with this that
works-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub
myRow = Target.Row - 1

If Application.Intersect(Range("b1:c150"), Target) Is Nothing Then
Exit Sub
End If

Set myRng = Me.Range(Cells(myRow, "D").Offset(1, 0), Cells(myRow,
"X").Offset(1, 0))

'stop the change from firing this event
Application.EnableEvents = False
myRng.Replace what:="L??O???", _
Replacement:=Me.Cells(Target.Row, "a").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

Target.Offset(1, 0).Select
Application.EnableEvents = True

End Sub

I was wondering if you could help me with on further issue?

The whole pont of this code is to replace part of a reference in a DDE link.
The number entered into col b is always the same and by using find and
replace these can be chaged quite easily and the code now works a dream.
The numbers entered into col c are usually sequential, I have tried using a
sum function in each of the cells following the first to add 1 to the number
entered into c5 an typing the first 2 entries then dragging the sequece down
the page but the code does'nt run, presumably because enter has to be hit to
trigger the code!
Is there a way of triggering the code when just the value has been changed?

Many thank in advance

Mark

 

[Dave Peterson]

If you're dragging down a few rows in column C, then the event will fire. But
this line:

If Target.Cells.Count > 1 Then Exit Sub

Will cause the routine to exit (there's more than one cell being changed).

 

[Guest]

Thanks once again Dave, I can see the importance of the line now you've
pointed it out.
Whilst testing this code i realise that by using a sum function in col c and
setting that col first then changing the value in col b with a command button
-

Private Sub CommandButton1_Click()
Range("b6:b150").Select
Selection.Replace what:="??", _
Replacement:=Cells("5", "b").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

The event fires and all the DDE links are updated so I've changed the range
to just look at col b.
Is there a way to incorporating the above code as the first step of the
event? this will fully automate the process of setting up the DDE links and
my job will be done!!

I'd just like to thank you for your assistance in this matter (and past
ones) and wondered if you could recommend any good tutorial books for a
begineer like me, I always feel like I'm getting people like yourself to do
all the hard work becuse my knowledge doesn't match my ideas!!

 

[Dave Peterson]

First, I've never worked with DDE links--so there might be better ways of
accomplishing what you want.

Second, Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

Third, you want this replace to run whenever you change any cell anywhere on the
worksheet?

Private Sub Worksheet_Change(ByVal Target As Range)
dim myRow as long
dim myRng as range

'stop the edit|replace from firing the worksheet_change
application.enableevents = false
me.Range("b6:b150").Replace what:="??", _
Replacement:=me.Cells("5", "b").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
application.enableevents = true

If Target.Cells.Count > 1 Then Exit Sub
myrow = target.row -1

'fix up the rest of the code the way you like????


If Application.Intersect(Range("b1:d150"), Target) Is Nothing Then
exit sub
end if

set myrng = me.range(.cells(myrow,"D"),.cells(myrow,"X"))

'stop the change from firing this event
application.enableevents = false
myrng.Replace what:="L??O???", _
Replacement:=me.Cells(target.row, "a").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

Target.Offset(1, 0).Select
application.enableevents =true

End Sub

Thanks once again Dave, I can see the importance of the line now you've
pointed it out.
Whilst testing this code i realise that by using a sum function in col c and
setting that col first then changing the value in col b with a command button
-

Private Sub CommandButton1_Click()
Range("b6:b150").Select
Selection.Replace what:="??", _
Replacement:=Cells("5", "b").Value, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End Sub

The event fires and all the DDE links are updated so I've changed the range
to just look at col b.
Is there a way to incorporating the above code as the first step of the
event? this will fully automate the process of setting up the DDE links and
my job will be done!!

I'd just like to thank you for your assistance in this matter (and past
ones) and wondered if you could recommend any good tutorial books for a
begineer like me, I always feel like I'm getting people like yourself to do
all the hard work becuse my knowledge doesn't match my ideas!!

 

[Guest]

Thanks for all your help everthing is working just great now.