What am I missing? VBA Intersect Target to run macro

[sxhall]

Hi,

I have the following code that should recognise a value change on a range of cells and then run a simple piece of code to add in a formula etc. However on the range I have given the cells have data validation applied but the user can also free type whatever they want in the cell. What I am struggling with is this code works when the data validation is used to change the cell value but does not work if I type in to the cell!!!

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Me.Range("$M$5:$M$65536")) Is Nothing Then
    
    Else
    
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False 'to prevent endless loop

        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = _
            "=IF(RC[-1]="""","""",IF(LEFT(RC[-1],4)=""WG10"",""Internal"",""External""))"
        ActiveCell.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveCell.Offset(0, -1).Select
    
    End If
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
       
End Sub

I need it to work if either the data validation drop-down is used or a user types in to a cell in the range given.

Thanks for any assistance in advance.

Steven

 

[Andrew Poulsom]

What makes you think that the code isn't working if you type in a cell?

 

[Jonmo1]

When you say "Doesn't work", what exactly do you mean?

Does nothing happen at all?
Or does the code still run, but doesn't do the correct action?

My guess is the use of ActiveCell.

When you change a cell with DataValidation drop down, the ActiveCell remailns the cell with the validation.
However, if you manually type in a cell, the ActiveCell MOVES down 1 cell when you press enter.

So it's likely that the code is running, but it's doing the work (enter formula, copy/paste special) on the wrong cell (1 cell down).


Try using TARGET instead of ActiveCell.
Target represents the cell that was changed..

 

[sxhall]

What makes you think that the code isn't working if you type in a cell?

I have tried it and I get nothing in the cell immediately to the right. Have tried typing a code that starts with 'WG10' and still it remains blank. If I copy and paste it in it picks that I have changed the cell, this works with anything I copy and paste, it then runs and adds in the formula to the right but just not when I type and then hit return!

 

[sxhall]

When you say "Doesn't work", what exactly do you mean?

Does nothing happen at all?
Or does the code still run, but doesn't do the correct action?

My guess is the use of ActiveCell.

When you change a cell with DataValidation drop down, the ActiveCell remailns the cell with the validation.
However, if you manually type in a cell, the ActiveCell MOVES down 1 cell when you press enter.

So it's likely that the code is running, but it's doing the work (enter formula, copy/paste special) on the wrong cell (1 cell down).


Try using TARGET instead of ActiveCell.
Target represents the cell that was changed..

SUPERB,

Can't believe it was so simple! Have been trying to figure this out for an hour and all the time it was working but as you pointed out on the cell below!!!!

Changed it to the below and now all runs fine

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Me.Range("$M$5:$M$65536")) Is Nothing Then
    
    Else
    
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False 

        Target.Offset(0, 1).Select  '** Changed here from 'Activcell' to 'Target' **
        ActiveCell.FormulaR1C1 = _
            "=IF(RC[-1]="""","""",IF(LEFT(RC[-1],4)=""WG10"",""Internal"",""External""))"
        ActiveCell.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveCell.Offset(0, -1).Select
    
    End If
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
       
End Sub

I can now get on with my day

Thanks Jonmo1

 

[Jonmo1]

You're welcome..

I'd also point out that the same task can be accomplished wihtout selecting anything.
You can just refer to and manipulate them directly.

try

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("$M$5:$M$65536")) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    With Target.Offset(0, 1)
        .FormulaR1C1 = _
            "=IF(RC[-1]="""","""",IF(LEFT(RC[-1],4)=""WG10"",""Internal"",""External""))"
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Application.CutCopyMode = False
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
   
End Sub

If you work to write code this way,
You'll never again have to worry about the ActiveCell NOT being the cell you think it is (which happens more often than you think).
And you won't have to worry about RE-Selecting the original cell before the code ran.

Not to mention, it's faster / more efficient.

 

[sxhall]

You're welcome..

I'd also point out that the same task can be accomplished wihtout selecting anything.
You can just refer to and manipulate them directly.

try

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("$M$5:$M$65536")) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    With Target.Offset(0, 1)
        .FormulaR1C1 = _
            "=IF(RC[-1]="""","""",IF(LEFT(RC[-1],4)=""WG10"",""Internal"",""External""))"
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    Application.CutCopyMode = False
End If

Application.EnableEvents = True
Application.ScreenUpdating = True
   
End Sub

If you work to write code this way,
You'll never again have to worry about the ActiveCell NOT being the cell you think it is (which happens more often than you think).
And you won't have to worry about RE-Selecting the original cell before the code ran.

Not to mention, it's faster / more efficient.

Thanks again Jonmo1,

Learning every day.

Steven