VBA not working as expected

[Russk68]

Hi All,
The code below in blue text is not working. I am expecting that when I make a change anywhere in column N that formulas in AW3:AW100 and BE3:BBE1000 will calculate.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(R, Target) Is Nothing Then

If Target.Count > 1 Then Exit Sub
If Target.Column <> 7 Then Exit Sub
If Target <> "" And Target.Offset(0, -6) = "" Then
Target.Offset(0, -6) = "Location Needed"
ElseIf Target = "" Then
Target.Offset(0, -6).ClearContents
End If

If Not Intersect(Target, Range("N:N")) Is Nothing Then
Range("AW3:AW1000,BE3:BE1000").Calculate
End If

If Not Intersect(Target, Range("p,s:s,v:v,y:y")) Is Nothing Then
Range("AS1:AS1000,AT1:AT1000,AU1:AU1000,AV1:AV1000").Calculate
End If

If Not Intersect(Target, Range("h:h,ao:ao")) Is Nothing Then
Range("c1:c1000,AR1:AR1000,Be1:Bh1000").Calculate
End If

If Not Intersect(Target, Range("a:a")) Is Nothing Then
Range("d1:d1000").Calculate 'Cable color
End If

For Each Cll In Range("AO3:AO50,BP2")
For Each TCll In Target
If Cll.Address = TCll.Address Then
ThisWorkbook.Sheets("Multi Build").Calculate
ThisWorkbook.Sheets("Distro").Calculate
Exit Sub
End If
Next TCll
Next Cll
End If
End Sub

 

[Paul Ked]

Please use code tags, it makes it much easier to read.

What is R?

 

[Michael M]

I'm guessing the code doesn't even get to those lines...have you stepped through the code ??
AND
As Paul has mentioned please use code tags when posting code........to do that paste your code into the post box>>highlight the code >>press the </> on the toolbar

 

[Russk68]

Hi All,
The code below in blue text is not working. I am expecting that when I make a change anywhere in column N that formulas in AW3:AW100 and BE3:BBE1000 will calculate.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(R, Target) Is Nothing Then If Target.Count > 1 Then Exit Sub If Target.Column <> 7 Then Exit Sub If Target <> "" And Target.Offset(0, -6) = "" Then Target.Offset(0, -6) = "Location Needed" ElseIf Target = "" Then Target.Offset(0, -6).ClearContents End If [COLOR=rgb(41, 105, 176)]If Not Intersect(Target, Range("N:N")) Is Nothing Then Range("AW3:AW1000,BE3:BE1000").Calculate End If[/COLOR] If Not Intersect(Target, Range("p:p,s:s,v:v,y:y")) Is Nothing Then Range("AS1:AS1000,AT1:AT1000,AU1:AU1000,AV1:AV1000").Calculate End If If Not Intersect(Target, Range("h:h,ao:ao")) Is Nothing Then Range("c1:c1000,AR1:AR1000,Be1:Bh1000").Calculate End If If Not Intersect(Target, Range("a:a")) Is Nothing Then Range("d1:d1000").Calculate 'Cable color End If For Each Cll In Range("AO3:AO50,BP2") For Each TCll In Target If Cll.Address = TCll.Address Then ThisWorkbook.Sheets("Multi Build").Calculate ThisWorkbook.Sheets("Distro").Calculate Exit Sub End If Next TCll Next Cll End If End Sub

 

[Paul Ked]

What is R?

as in

If Not Intersect(R, Target) Is Nothing Then

And as Michael asked, have you stepped through the code?

 

[Russk68]

Please use code tags, it makes it much easier to read.

What is R?

I don't know what the R is for? I've been piecing this together over the years. I don't know VBA and I get help from here and Google searches.

 

[Russk68]

as in

And as Michael asked, have you stepped through the code?

I'm on a MAC. How do I step through the code?

 

[Russk68]

Sorry!
I left out some code at the top

Dim R As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set R = Selection.Cells(1, 1)
Application.Calculation = xlCalculationManual
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(R, Target) Is Nothing Then
        


If Target.Count > 1 Then Exit Sub   
If Target.Column <> 7 Then Exit Sub
If Target <> "" And Target.Offset(0, -6) = "" Then
    Target.Offset(0, -6) = "Location Needed"
ElseIf Target = "" Then
     Target.Offset(0, -6).ClearContents
End If

If Not Intersect(Target, Range("N:N")) Is Nothing Then
        Range("AW3:AW1000,BE3:BE1000").Calculate
    End If

 
    If Not Intersect(Target, Range("p:p,s:s,v:v,y:y")) Is Nothing Then 
        Range("AS1:AS1000,AT1:AT1000,AU1:AU1000,AV1:AV1000").Calculate
    End If
    
       If Not Intersect(Target, Range("h:h,ao:ao")) Is Nothing Then 
        Range("c1:c1000,AR1:AR1000,Be1:Bh1000").Calculate
       End If
       
       If Not Intersect(Target, Range("a:a")) Is Nothing Then 
        Range("d1:d1000").Calculate 'Cable color
       End If
       
      For Each Cll In Range("AO3:AO50,BP2")
        For Each TCll In Target
        If Cll.Address = TCll.Address Then
            ThisWorkbook.Sheets("Multi Build").Calculate
            ThisWorkbook.Sheets("Distro").Calculate
            Exit Sub
        End If
        Next TCll
    Next Cll

 

[Paul Ked]

Not sure if it's the same as PC but try these steps:

In the VBE click on the margin to the left of

If Not Intersect(R, Target) Is Nothing Then

it should be highlighted and a dot placed in the margin:

When you make a change on the worksheet the procedure will halt at that line anb be highlighted in yellow:

Press F8 to step through the code and see where it errors/exits.

PS ignore the Dim r, that was added by me!

 

[Paul Ked]

OK, the R is a Range!

The sub is exiting because the column number for N is 19 which does not = 7 in this line

If Target.Column <> 7 Then Exit Sub

I don't know why that is there, but try commenting it out (put a ' (single quote) in front of the line) and see what happens!

'If Target.Column <> 7 Then Exit Sub