+ Reply to Thread
Results 1 to 6 of 6

Cell change event

  1. #1
    gig
    Guest

    Cell change event

    I keep getting an error message when range("attn") is changed. If I
    delete the vlookup formula line and put something like msgbox "hello"
    it will work fine. But what puzzles me is that the same vlookup line
    formula works in another program with no problems. Can anyone tell me
    what I'm doing wrong?

    Thanks,
    Greg


    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim company As Range
    Dim database As Range
    Dim attn As Range

    If Target.Address = Range("attn").Address Then

    Range("company") = Application.WorksheetFunction.VLookup(Range("attn"),
    Range("database"), 2, False)
    End If

    End Sub


  2. #2
    ben
    Guest

    RE: Cell change event

    gig,
    what error message are you recieving?

    "gig" wrote:

    > I keep getting an error message when range("attn") is changed. If I
    > delete the vlookup formula line and put something like msgbox "hello"
    > it will work fine. But what puzzles me is that the same vlookup line
    > formula works in another program with no problems. Can anyone tell me
    > what I'm doing wrong?
    >
    > Thanks,
    > Greg
    >
    >
    > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    >
    > Dim company As Range
    > Dim database As Range
    > Dim attn As Range
    >
    > If Target.Address = Range("attn").Address Then
    >
    > Range("company") = Application.WorksheetFunction.VLookup(Range("attn"),
    > Range("database"), 2, False)
    > End If
    >
    > End Sub
    >
    >


  3. #3
    gig
    Guest

    Re: Cell change event

    The error message I keep getting is:

    Run time error '1004':

    Method 'Range' of object'_worksheet' failed


    Thanks


  4. #4
    ben
    Guest

    Re: Cell change event

    it sounds like one of your named ranges is not available, generally that
    error means that Excel cannot find or cannot select the specified range, also
    you might want to consider not naming your ranges in your code to the same
    exact names you have in your spreadsheet defined names. So make sure to check
    that named ranges are not mispelled.

    "gig" wrote:

    > The error message I keep getting is:
    >
    > Run time error '1004':
    >
    > Method 'Range' of object'_worksheet' failed
    >
    >
    > Thanks
    >
    >


  5. #5
    Tom Ogilvy
    Guest

    Re: Cell change event

    That means the vlookup can't find the value of Range("attn") in the lookup
    range.

    You need to handle the error

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim company As Range
    Dim database As Range
    Dim attn As Range

    If Target.Address = Range("attn").Address Then
    On Error Resume Next
    Range("company") = Application.WorksheetFunction _
    .VLookup(Range("attn"),Range("database"), 2, False)
    End If
    On Error goto 0
    End Sub

    Another problem can be that if these named ranges are not on the worksheet
    containing the code, they need to be qualified with the worksheet on which
    they are located.
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim company As Range
    Dim database As Range
    Dim attn As Range
    Dim sh1 as Worksheet
    Dim sh2 as Worksheet
    Set Sh1 = Worksheets("Database")
    Set sh2 = Worksheets("Sheet3")

    If Target.Address = Range("attn").Address Then
    On Error Resume Next
    sh2.Range("company") = Application.WorksheetFunction _
    .VLookup(Range("attn"),sh1.Range("database"), 2, False)
    End If
    On Error goto 0
    End Sub

    as an example.

    --
    Regards,
    Tom Ogilvy



    "gig" <[email protected]> wrote in message
    news:[email protected]...
    > The error message I keep getting is:
    >
    > Run time error '1004':
    >
    > Method 'Range' of object'_worksheet' failed
    >
    >
    > Thanks
    >




  6. #6
    gig
    Guest

    Re: Cell change event

    Thanks for your input Ben and Tom. I need to study this in detail now
    and see what's going on.

    Much appreciated,

    Greg


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1