Worksheet Change Event Intersect Error 91

  • Hi There
    This is my code:


    when excution comes to this line

    Code
    If Intersect(Target, Range("D6:D6000")) Is Nothing Or Intersect(Target, Range("D6:D6000")) = "" Then

    a run time error (91) appears, it tells object variable or with block variable not set.


    any idea why ? Thank you

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

  • Re: Run Time Error 91


    Thanks 4 reply


    i corrected my code, but still the error msg appear ???

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

  • Re: Run Time Error 91


    If the result of the intersect is nothing then the second part of the test will fail.


    Also the second part of the test will not do what you want. It will not check all cells in the rather being empty.


    [vba] If Intersect(Target, Range("D6:D6000")) Is Nothing Or _
    Application.WorksheetFunction.CountA(Range("D6:D6000")) = 0 Then
    If Intersect(Target, Range("F6:F6000")) Is Nothing Or _
    Application.WorksheetFunction.CountA(Range("F6:F6000")) = 0 Then
    If Intersect(Target, Range("I6:I6000")) Is Nothing Or _
    Application.WorksheetFunction.CountA(Range("I6:I6000")) = 0 Then
    Exit Sub
    Else

    MsgBox "Do Something......"
    End If
    End If
    End If
    [/vba]

  • Re: Run Time Error 91


    i did understand you quit well, it worked good
    Andy, I donot know what to say to thank you, bt thank you very very much
    :gift:

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

  • Re: Run Time Error 91


    Hi


    if i delete a row within the Target, then the code trigered..........


    is there a way stop the code excution when i delete a row within the target ?

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

  • Re: Worksheet Change Event Intersect Error 91


    Helmekki,


    Please take care with your thread titles.


    Can your code be simplified to this?


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("D6:D6000,F6:F6000,I6:I6000")) Is Nothing Or _
           Application.WorksheetFunction.CountA(Range("D6:D6000,F6:F6000,I6:I6000")) = 0 Then
            Exit Sub
        Else
            MsgBox "Do Something......"
        End If
    End Sub
  • Re: Worksheet Change Event Intersect Error 91


    I havnt tested this (But its an idea):


  • Re: Worksheet Change Event Intersect Error 91


    Reafidy , thank you very much it worked fine ;)

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

  • Re: Worksheet Change Event Intersect Error 91



    Your original code was not a Worksheet change event, hence my response that Target
    was NOT defined ...


    You had


    Code
    Sub T()
    etc .............
  • Re: Worksheet Change Event Intersect Error 91


    I apologise Ivan..........i shoud have made clear from the start:rolleyes:


    Ok ............it was misorganised posting...............:(

    will take care of such posts .:cool:


    Thanks

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!