Select method of range class failed

S

sa02000

I keep getting this error
Run-time error '1004':
Select method of Range class failed

from this code at the first Range("I2").select statement. I dont see
what is wrong. Please help!!




Sub Import

Dim strname As String

strname = ActiveWorkbook.Name
Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
' wkbBook = Workbooks("Sep_download.xls")

Workbooks("Sep_download.xls").Worksheets(1).Copy
before:=Workbooks(strname).Worksheets(3)
Workbooks("Sep_download.xls").Close savechanges:=True

Sheets("sheet2").Select
ActiveWindow.SelectedSheets.Delete

Sheets("sep_download").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "=Trim(RC[-8])"
Range("I2").Select
Call GetRealLastCell
Selection.AutoFill Destination:=Range(I2, RealLastRow)



End Sub

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub
 
J

Jim Thomlinson

There are a couple of problems in your code... Here is a fixed up version
(Untested but it should be close)

Sub Import
Dim wbkOpened as workbook
Dim rngLast as Range

Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
wbkOpened = Workbooks("Sep_download.xls")

wbkOpened .Worksheets(1).Copy
before:=Thisworkbook.Worksheets(3)
wbkOpened.Close savechanges:=True

Thisworkbook.Sheets("sheet2").Delete
set rngLast = LastCell(Sheets("sep_download"))

Sheets("sep_download").Range("I2", rngLast .Row).FormulaR1C1 = "=Trim(RC[-8])"

End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
 
Z

Zack Barresse

wbkOpened = Workbooks("Sep_download.xls")

... will need to be ...

Set wbkOpened = Workbooks("Sep_download.xls")

Also, I would think about using Application.DisplayAlerts = False when
deleting sheets (not forgetting to set it back to True at procedure end). I
always hated those pesky displayed alerts. <g>

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


Jim Thomlinson said:
There are a couple of problems in your code... Here is a fixed up version
(Untested but it should be close)

Sub Import
Dim wbkOpened as workbook
Dim rngLast as Range

Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
wbkOpened = Workbooks("Sep_download.xls")

wbkOpened .Worksheets(1).Copy
before:=Thisworkbook.Worksheets(3)
wbkOpened.Close savechanges:=True

Thisworkbook.Sheets("sheet2").Delete
set rngLast = LastCell(Sheets("sep_download"))

Sheets("sep_download").Range("I2", rngLast .Row).FormulaR1C1 =
"=Trim(RC[-8])"

End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


sa02000 said:
I keep getting this error
Run-time error '1004':
Select method of Range class failed

from this code at the first Range("I2").select statement. I dont see
what is wrong. Please help!!




Sub Import

Dim strname As String

strname = ActiveWorkbook.Name
Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
' wkbBook = Workbooks("Sep_download.xls")

Workbooks("Sep_download.xls").Worksheets(1).Copy
before:=Workbooks(strname).Worksheets(3)
Workbooks("Sep_download.xls").Close savechanges:=True

Sheets("sheet2").Select
ActiveWindow.SelectedSheets.Delete

Sheets("sep_download").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "=Trim(RC[-8])"
Range("I2").Select
Call GetRealLastCell
Selection.AutoFill Destination:=Range(I2, RealLastRow)



End Sub

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=27747
View this thread:
http://www.excelforum.com/showthread.php?threadid=473119
Click to expand...
Click to expand...
 
J

Jim Thomlinson

Nice catch on the set statement... I left the display alerts out on purpose
as that is how it was originally written. I tried not to change the way it
worked, just make it work. Also with display alerts it should have an
errorhandler which I was too lazy to add (mostly because of the latter)...
--
HTH...

Jim Thomlinson


Zack Barresse said:
wbkOpened = Workbooks("Sep_download.xls")
Click to expand...

... will need to be ...

Set wbkOpened = Workbooks("Sep_download.xls")

Also, I would think about using Application.DisplayAlerts = False when
deleting sheets (not forgetting to set it back to True at procedure end). I
always hated those pesky displayed alerts. <g>

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


Jim Thomlinson said:
There are a couple of problems in your code... Here is a fixed up version
(Untested but it should be close)

Sub Import
Dim wbkOpened as workbook
Dim rngLast as Range

Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
wbkOpened = Workbooks("Sep_download.xls")

wbkOpened .Worksheets(1).Copy
before:=Thisworkbook.Worksheets(3)
wbkOpened.Close savechanges:=True

Thisworkbook.Sheets("sheet2").Delete
set rngLast = LastCell(Sheets("sep_download"))

Sheets("sep_download").Range("I2", rngLast .Row).FormulaR1C1 =
"=Trim(RC[-8])"

End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


sa02000 said:
I keep getting this error
Run-time error '1004':
Select method of Range class failed

from this code at the first Range("I2").select statement. I dont see
what is wrong. Please help!!




Sub Import

Dim strname As String

strname = ActiveWorkbook.Name
Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
' wkbBook = Workbooks("Sep_download.xls")

Workbooks("Sep_download.xls").Worksheets(1).Copy
before:=Workbooks(strname).Worksheets(3)
Workbooks("Sep_download.xls").Close savechanges:=True

Sheets("sheet2").Select
ActiveWindow.SelectedSheets.Delete

Sheets("sep_download").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "=Trim(RC[-8])"
Range("I2").Select
Call GetRealLastCell
Selection.AutoFill Destination:=Range(I2, RealLastRow)



End Sub

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=27747
View this thread:
http://www.excelforum.com/showthread.php?threadid=473119
Click to expand...
Click to expand...
Click to expand...
 
Z

Zack Barresse

Agreed on the error handler. I see a lot of room for errors. (Probably
because I've screwed up more than my fair shar. hehe)

Take care Jim, hope all is well.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


Jim Thomlinson said:
Nice catch on the set statement... I left the display alerts out on
purpose
as that is how it was originally written. I tried not to change the way it
worked, just make it work. Also with display alerts it should have an
errorhandler which I was too lazy to add (mostly because of the latter)...
--
HTH...

Jim Thomlinson


Zack Barresse said:
wbkOpened = Workbooks("Sep_download.xls")
Click to expand...

... will need to be ...

Set wbkOpened = Workbooks("Sep_download.xls")

Also, I would think about using Application.DisplayAlerts = False when
deleting sheets (not forgetting to set it back to True at procedure end).
I
always hated those pesky displayed alerts. <g>

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


Jim Thomlinson said:
There are a couple of problems in your code... Here is a fixed up
version
(Untested but it should be close)

Sub Import
Dim wbkOpened as workbook
Dim rngLast as Range

Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
wbkOpened = Workbooks("Sep_download.xls")

wbkOpened .Worksheets(1).Copy
before:=Thisworkbook.Worksheets(3)
wbkOpened.Close savechanges:=True

Thisworkbook.Sheets("sheet2").Delete
set rngLast = LastCell(Sheets("sep_download"))

Sheets("sep_download").Range("I2", rngLast .Row).FormulaR1C1 =
"=Trim(RC[-8])"

End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


:


I keep getting this error
Run-time error '1004':
Select method of Range class failed

from this code at the first Range("I2").select statement. I dont see
what is wrong. Please help!!




Sub Import

Dim strname As String

strname = ActiveWorkbook.Name
Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
' wkbBook = Workbooks("Sep_download.xls")

Workbooks("Sep_download.xls").Worksheets(1).Copy
before:=Workbooks(strname).Worksheets(3)
Workbooks("Sep_download.xls").Close savechanges:=True

Sheets("sheet2").Select
ActiveWindow.SelectedSheets.Delete

Sheets("sep_download").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "=Trim(RC[-8])"
Range("I2").Select
Call GetRealLastCell
Selection.AutoFill Destination:=Range(I2, RealLastRow)



End Sub

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=27747
View this thread:
http://www.excelforum.com/showthread.php?threadid=473119
Click to expand...
Click to expand...
Click to expand...
 
J

Jim Thomlinson

I don't know about you but my code never creates errors. The users creates
errors all of the time. So now I just have to figure out how to get rid of
the end users and it wil be smooth sailing... ;-) Catch you later... Tomorrow
most likely.

--
HTH...

Jim Thomlinson


Zack Barresse said:
Agreed on the error handler. I see a lot of room for errors. (Probably
because I've screwed up more than my fair shar. hehe)

Take care Jim, hope all is well.

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


Jim Thomlinson said:
Nice catch on the set statement... I left the display alerts out on
purpose
as that is how it was originally written. I tried not to change the way it
worked, just make it work. Also with display alerts it should have an
errorhandler which I was too lazy to add (mostly because of the latter)...
--
HTH...

Jim Thomlinson


Zack Barresse said:
wbkOpened = Workbooks("Sep_download.xls")

... will need to be ...

Set wbkOpened = Workbooks("Sep_download.xls")

Also, I would think about using Application.DisplayAlerts = False when
deleting sheets (not forgetting to set it back to True at procedure end).
I
always hated those pesky displayed alerts. <g>

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)


There are a couple of problems in your code... Here is a fixed up
version
(Untested but it should be close)

Sub Import
Dim wbkOpened as workbook
Dim rngLast as Range

Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
wbkOpened = Workbooks("Sep_download.xls")

wbkOpened .Worksheets(1).Copy
before:=Thisworkbook.Worksheets(3)
wbkOpened.Close savechanges:=True

Thisworkbook.Sheets("sheet2").Delete
set rngLast = LastCell(Sheets("sep_download"))

Sheets("sep_download").Range("I2", rngLast .Row).FormulaR1C1 =
"=Trim(RC[-8])"

End Sub

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)

End Function
--
HTH...

Jim Thomlinson


:


I keep getting this error
Run-time error '1004':
Select method of Range class failed

from this code at the first Range("I2").select statement. I dont see
what is wrong. Please help!!




Sub Import

Dim strname As String

strname = ActiveWorkbook.Name
Workbooks.Open "C:\Macro_Practice\Sep_download.xls"
' wkbBook = Workbooks("Sep_download.xls")

Workbooks("Sep_download.xls").Worksheets(1).Copy
before:=Workbooks(strname).Worksheets(3)
Workbooks("Sep_download.xls").Close savechanges:=True

Sheets("sheet2").Select
ActiveWindow.SelectedSheets.Delete

Sheets("sep_download").Select
Range("I2").Select
ActiveCell.FormulaR1C1 = "=Trim(RC[-8])"
Range("I2").Select
Call GetRealLastCell
Selection.AutoFill Destination:=Range(I2, RealLastRow)



End Sub

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub


--
sa02000
------------------------------------------------------------------------
sa02000's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=27747
View this thread:
http://www.excelforum.com/showthread.php?threadid=473119
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select method of range class failed 1
Modify Function to Return Value 2
Copy a range of information to a worksheet in a specific file 1
Count Duplicates 8
Delete Row If Blank or Zero 1
VLOOKUP and delete row if found 5
Selecting a Range 4
Select method failed - Error 400 2

Top