Thanks Ron and Samura. That worked.

"Rob Bovey" wrote in message
...
Change the line:

Set myRng = myWs.Range(myWs.Cells(1, 2)) '<== Error 1004

to:

Set myRng = myWs.Cells(1, 2)

The first line of code is feeding a Range object to the Range property of
a
Worksheet. This is not allowed.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"news.easynews.com" wrote in message
...
Why would the following code give the error: 1004: Method 'Range' of
object
'_Worksheet' failed


Dim myExcel As Excel.Application
Dim myWb As Excel.Workbook
Dim myWs As Excel.Worksheet
Dim myRng As Excel.Range

Set myExcel = New Excel.Application
Set myWb = myExcel.Workbooks.Open("d:\test.csv")
Set myWs = myWb.Sheets(1)
Set myRng = myWs.Range(myWs.Cells(1, 2)) '<== Error 1004

MsgBox myRng.Address


If I change the line giving the error to

Set myRng = myWs.Range("B1")

then it's ok. Any suggestions?


Thanks!