Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Select method of range class failed
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Select method of range class failed

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
befo=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...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=473119

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default Select method of range class failed
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
befo=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" wrote:


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
befo=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...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=473119


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 124
Default Select method of range class failed
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" wrote in message
...
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
befo=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" wrote:


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
befo=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...o&userid=27747
View this thread:
http://www.excelforum.com/showthread...hreadid=473119




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default Select method of range class failed
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" wrote:

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" wrote in message
...
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
befo=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" wrote:


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
befo=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...o&userid=27747
View this thread:
http://www.excelforum.com/showthread...hreadid=473119





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 124
Default Select method of range class failed
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" wrote in message
...
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" wrote:

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" wrote in message
...
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
befo=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" wrote:


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
befo=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...o&userid=27747
View this thread:
http://www.excelforum.com/showthread...hreadid=473119







Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default Select method of range class failed
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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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
befo=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" wrote:


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
befo=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...o&userid=27747
View this thread:
http://www.excelforum.com/showthread...hreadid=473119








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select method of range class failed sa02000[_4_] Excel Programming 1 October 5th 05 01:20 PM
Select method of Range class failed - but why??? Orion[_2_] Excel Programming 3 December 21st 04 03:28 PM
select method of range class failed Joseph[_40_] Excel Programming 0 September 28th 04 04:08 PM
select method of range class failed Joseph[_38_] Excel Programming 1 September 28th 04 03:21 PM
select method of range class failed Joseph[_39_] Excel Programming 0 September 28th 04 02:32 PM


All times are GMT +1. The time now is 01:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement