PDA

View Full Version : Solved: Change range in code



Shazam
06-30-2006, 11:00 AM
This is probably a easy question. But how to have this worksheet event code to start at B2 and down. Right now its working at the whole column B I just would like to start at B2:B40000




Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
Application.DisplayAlerts = False
Dim rng As Range
If Target.Count > 2 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Target.Offset(0, -1).FillDown 'A
Target.Offset(0, 2).FillDown 'D
Target.Offset(0, 3).FillDown 'E
Target.Offset(0, 4).FillDown 'F
Target.Offset(0, 11).FillDown 'M
Target.Offset(0, 12).FillDown 'N
Target.Offset(0, 13).FillDown 'O
Target.Offset(0, 14).FillDown 'P

Application.DisplayAlerts = True
GetOut:

End Sub

Zack Barresse
06-30-2006, 11:03 AM
If Intersect(Target, Me.Range("B2:B40000")) Is Nothing Then exit sub

:)

mdmackillop
06-30-2006, 11:30 AM
Hi Shazam
You need to disable Events or your code will restat when the first copydown occurs.
It's neater to use an array and loop through for code like this


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
Application.EnableEvents = False
Dim Cols, c
Cols = Array(-1, 2, 3, 4, 11, 12, 13, 14)
Dim rng As Range
If Target.Count > 2 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
For Each c In Cols
Target.Offset(0, c).FillDown
Next
GetOut:
Application.EnableEvents = True
End Sub

Shazam
06-30-2006, 12:04 PM
Thanks to you both.

mdmackillop,

I have seen the Application.EnableEvents = False Before but I just dont really understand what does it do. Can you give me some secnarios so I could have a better understanding of it?

Thanks!



Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto GetOut
Application.EnableEvents = False
Dim Cols, c
Cols = Array(-1, 2, 3, 4, 11, 12, 13, 14)


If Intersect(Target, Me.Range("B2:B40000")) Is Nothing Then exit Sub
For Each c In Cols
Target.Offset(0, c).FillDown
Next
GetOut:
Application.EnableEvents = True
End Sub

mdmackillop
06-30-2006, 12:24 PM
An Event macro will run whenever the apprpriate Event occurs. In your case, whenever the worksheet changes. As Target.Offset(0, c).FillDown changes the worksheet, your code will stop and the Event code starts again, leading to errors/freezing. Try stepping through the code to see what happens.
As soon as you have checked that your macro should run, you should then disable events. You must re-enable them, as this will not happen automatically, and your error handling should also re-enable them before exiting the routine.

Shazam
06-30-2006, 12:29 PM
Thanks mdmackillop I will follow your instructions.

mdmackillop
06-30-2006, 03:07 PM
Hi Shazam
You should check validity and exit before your main code is run. You also have to avoid Exit Sub after you have disabled events. To terminate the code you must use a GoTo GetOut method in order to re-enable events.
Private Sub Worksheet_Change(ByVal Target As Range)
'Check validity
If Intersect(Target, Me.Range("B2:B40000")) Is Nothing Then Exit Sub

On Error GoTo GetOut
Application.EnableEvents = False
Dim Cols, c
Cols = Array(-1, 2, 3, 4, 11, 12, 13, 14)
For Each c In Cols
Target.Offset(0, c).FillDown
Next
GetOut:
Application.EnableEvents = True
End Sub