Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell change event
The error message I keep getting is:
Run time error '1004': Method 'Range' of object'_worksheet' failed Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ups.com... The error message I keep getting is: Run time error '1004': Method 'Range' of object'_worksheet' failed Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell change event | Excel Worksheet Functions | |||
cell value change event | Excel Worksheet Functions | |||
CELL CHANGE EVENT | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
Cell Change Event | Excel Programming |