Intersect(Target, TargetField) run-time error (1004)

[rob_grimshaw]

Dear all,

I am getting a run-time error for this code, can you help tell me why and how to fix it?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     
    If ActiveSheet.Name <> "Form" Then End


    Dim Form As Worksheet
    Dim Data As Worksheet
    
    Set Form = ThisWorkbook.Worksheets("Form")
    Set Data = ThisWorkbook.Worksheets("Data")


    Dim TargetField As Range
    Set TargetField = Form.Range("E4")


    Dim SourceField As Range

    Set SourceField = Data.Range("A2:A434").Find _
                                                (What:=TargetField, _
                                                LookIn:=xlValues, _
                                                LookAt:=xlWhole, _
                                                SearchOrder:=xlByRows, _
                                                MatchCase:=False)


    If Not Intersect(Target, TargetField) Is Nothing Then
            
            Form.Range("I6") = SourceField.Offset(, 29)


            Else

                Set TargetField = Form.Range("I6")
                
                If Not Intersect(Target, TargetField) Is Nothing Then


                    SourceField.Offset(, 29) = Form.Range("I6").Value


                End If
    End If


End Sub

The error reads: "Run-time error 1004: Method 'Intersect' of object '_Global' failed"

It still fails even if I change "

SourceField.Offset(, 29) = Form.Range("I6").Value" to "Offset (,30)" to try and prevent a feedback loop.​

Many thanks.

 

[Fluff]

Which line gives the error?

 

[rob_grimshaw]

The debugger highlights the first "

If Not Intersect(Target, TargetField) Is Nothing Then"
but working through the code I believe that the error is caused at
"

SourceField.Offset(, 29) = Form.Range("I6").Value"​

​

 

[Fluff]

The debugger highlights the line that is causing the problem.
As you only want this to work on the sheet called "Form" are you happy to have the code in that sheet module, rather than in the workbook module?

 

[rob_grimshaw]

The debugger highlights the line that is causing the problem.
As you only want this to work on the sheet called "Form" are you happy to have the code in that sheet module, rather than in the workbook module?

Yes, absolutely.

 

[Fluff]

Ok remove the existing code from the ThisWorkbook module & put this in the "Form" sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim SourceField As Range
   Dim Data As Worksheet
   
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("E4, I6")) Is Nothing Then
      Set Data = ThisWorkbook.Worksheets("Data")
      Set SourceField = Data.Range("A2:A434").Find(Range("E4").Value, , , xlWhole, , , False, , False)
      If SourceField Is Nothing Then
         MsgBox "Not found"
         Exit Sub
      End If
      Select Case Target.Address(0, 0)
         Case "E4"
            Application.EnableEvents = False
            Range("I6").Value = SourceField.Offset(, 29).Value
            Application.EnableEvents = True
         Case "I6"
            SourceField.Offset(, 29) = Range("I6").Value
      End Select
   End If
End Sub

 

[rob_grimshaw]

Thanks for this.

For my learning, what was the problem with the original code?

 

[Fluff]

The code was changing the Data sheet, which would trigger the code, the first line checks if the Active is Form (which it is), but as the Target is on sheet Data, you get the error as the intersect cannot handle ranges on separate sheets.

 

[rob_grimshaw]

Thanks

 

[Fluff]

You're welcome