Can anyone tell me why the very same sub gives different results whe
run from the Personal.xls workbook? Here is the code:

Sub VLookUpRange()
Dim rName As Name
Set rName = ThisWorkbook.Names("WhatEver")
End Sub

This sub works fine with all workbooks except Personal.xls.

I get the 'Run time error 1004 - application defined or object-define
error' when I try to run it from Personal.xl

--
Message posted from http://www.ExcelForum.com

Hi DredanZyl

Do you have a defined name called "Whatever" in "Personal.xls" since your
code refers to that name in "ThisWorkbook" which is the workbook executing
the sub. Do you mean "ActiveWorkbook".

--
XL2002
Regards

William



"DredanZyl " wrote in message
...
| Can anyone tell me why the very same sub gives different results when
| run from the Personal.xls workbook? Here is the code:
|
| Sub VLookUpRange()
| Dim rName As Name
| Set rName = ThisWorkbook.Names("WhatEver")
| End Sub
|
| This sub works fine with all workbooks except Personal.xls.
|
| I get the 'Run time error 1004 - application defined or object-defined
| error' when I try to run it from Personal.xls
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

Perhaps Personal.xls doesn't have a name called 'WhatEver'. This is where
you need error checking, like so

On Error Resum Next
Set rName = ThisWorkbook.Names("WhatEver")
If rName Is Nothing Then
MsgBox "Ooops!"
End If

It is probable that you don't mean ThisWorkbook which is the workbook the
code is in, buty ACtiveWorkbook, which is the one you are working on.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"DredanZyl " wrote in message
...
Can anyone tell me why the very same sub gives different results when
run from the Personal.xls workbook? Here is the code:

Sub VLookUpRange()
Dim rName As Name
Set rName = ThisWorkbook.Names("WhatEver")
End Sub

This sub works fine with all workbooks except Personal.xls.

I get the 'Run time error 1004 - application defined or object-defined
error' when I try to run it from Personal.xls


---
Message posted from http://www.ExcelForum.com/

Thanks William, that was it. I should have put 'Active workbook'.







William wrote:
*Hi DredanZyl

Do you have a defined name called "Whatever" in "Personal.xls" sinc
your
code refers to that name in "ThisWorkbook" which is the workboo
executing
the sub. Do you mean "ActiveWorkbook".

--
XL2002
Regards

William



"DredanZyl " wrote i
message
...
| Can anyone tell me why the very same sub gives different result
when
| run from the Personal.xls workbook? Here is the code:
|
| Sub VLookUpRange()
| Dim rName As Name
| Set rName = ThisWorkbook.Names("WhatEver")
| End Sub
|
| This sub works fine with all workbooks except Personal.xls.
|
| I get the 'Run time error 1004 - application defined o
object-defined
| error' when I try to run it from Personal.xls
|
|
| ---
| Message posted from http://www.ExcelForum.com/
|

--
Message posted from http://www.ExcelForum.com