Why is the following code giving an error 91?

[vbaNumpty]

I can't figure out what I haven't defined properly.

The line:

If Not (Intersect(Target, Range("H" & rowNum))) Is Nothing Then

Gives the error.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rowNum As Integer
    Dim cellResult As Variant
    rowNum = ActiveCell.Row - 1
    Dim fromSales As Boolean
    fromSales = Range("A" & rowNum).Value
    
    'On Change Cutting delivery date
    If Not (Intersect(Target, Range("H" & rowNum))) Is Nothing Then
    'If Target.Address = Range("H:H") Then
        Call UpdateDeliveryWeek(rowNum)
        If (fromSales = False) And (Range("B" & rowNum) = "") Or (Range("C" & rowNum) = "") Or (Range("D" & rowNum) = "") Or (Range("H" & rowNum) = "") Then
            Exit Sub
        Else
            Call UpdateAllDatesFromStart(rowNum)
        End If
    End If

 

[Fluff]

Remove the outer set of brackets. But what are you trying to do with that line?

 

[vbaNumpty]

Remove the outer set of brackets. But what are you trying to do with that line?

Always the syntax getting me. The line is one in a long list of checks to see if the cells in that row need data. The file used to be nothing but long formulas that made it run to a crawl so this is an attempt to get it to only put values where needed and no formulas that slow the file down.

 

[Fluff]

If the user tabs, or clicks out of the cell, then you may be looking at the wrong row.

 

[vbaNumpty]

If the user tabs, or clicks out of the cell, then you may be looking at the wrong row.

What do you mean? They are primarily dates that are entered in the cell and then it fetches the appropriate information for that row.

 

[Fluff]

You are using this line rowNum = ActiveCell.Row - 1 to determine which row to look at.
If the user edits B2 & then clicks into B10 your code will be looking at row 9 & not row2

 

[vbaNumpty]

You are using this line rowNum = ActiveCell.Row - 1 to determine which row to look at.
If the user edits B2 & then clicks into B10 your code will be looking at row 9 & not row2

I've found with the change event with the way I have it set with the Not intersect it looks at the row beneath the one I wanted, adding the -1 was the way to get it to look at the proper line. Haven't come across issues yet, but I will keep that in mind when we start testing it upon completion.

Might use something other than activecell.

 

[Fluff]

Just use

    If Not Intersect(Target, Range("H" & Target.Row)) Is Nothing Then

 

[vbaNumpty]

Just use

    If Not Intersect(Target, Range("H" & Target.Row)) Is Nothing Then

Awesome, thanks a lot. Appreciate you.

 

[Fluff]

You're welcome & thanks for the feedback.