MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Run Time Error, help!


Posted by RoB on October 08, 2001 9:44 AM

Hi again,

I posted about this problem 630.html, but never got it resolved.

I'm getting the error "Run-Time error '1004': Select Method of Range class failed".

This is my code:

Sub Directory_Search()

Dim myFile As String, myCurrFile As String
myCurrFile = ThisWorkbook.Name
myFile = Dir("S:\FileServer\Excel\Save FILES In Here\YEAR 2001\01-01\Cathy\*.xls")
Do Until myFile = ""
Workbooks.Open "S:\FileServer\Excel\Save FILES In Here\YEAR 2001\01-01\Cathy\" & myFile


Workbooks(myCurrFile).Worksheets("Sheet1").Range("A65536").End(xlUp).Select
With ActiveCell
.Offset(1, 0).Value = Workbooks(myFile).Worksheets("Application").Range("AI4").Value
End With


Workbooks(myFile).Close savechanges:=False
myFile = Dir
Loop

End Sub

it says the problem is with the bold part. I have tried using .value and .formula for the formula part, and also .select and .activate to select the cell, but I'm still getting the error.

Can anyone help me? Thanks


Posted by Juan Pablo on October 08, 2001 9:48 AM

Put this before the line

Workbooks(myCurrFile).Worksheets("Sheet1").Activate

Juan Pablo

Posted by RoB on October 08, 2001 9:56 AM

LOL

I just figured it out a few min after i posted. I guess that should have been obvious, after i open a file, i guess it would be active :P

Thanks the the quick response though Juan!