Why is the following code giving an error 91?

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
171
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Remove the outer set of brackets. But what are you trying to do with that line?
 
Upvote 0
Solution
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.
 
Upvote 0
If the user tabs, or clicks out of the cell, then you may be looking at the wrong row.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Just use
VBA Code:
    If Not Intersect(Target, Range("H" & Target.Row)) Is Nothing Then
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top