Results 1 to 10 of 10

Thread: Intersect(Target, TargetField) run-time error (1004)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Dear all,

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


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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    Which line gives the error?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    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"

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Jan 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    Quote Originally Posted by Fluff View Post
    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    Ok remove the existing code from the ThisWorkbook module & put this in the "Form" sheet module
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Jan 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    Thanks for this.

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

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Jan 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    Thanks

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,123
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Intersect(Target, TargetField) run-time error (1004)

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •