Excel VB Run Time Error 1004 - RESOLVED
Good morning.
I have a ? regarding a run time error (1004) in VB for Excel. I have a macro that runs fine through Alt F8. I created a control button and attached the macro. When I use the control button I get run time error 1004 with the message that says "Select method of range class failed." I am new at VB and any suggestions would be greatly appreciated. Thanks in advance for your assistance.
:)
-Helene
Re: Excel VB Run Time Error 1004 - RESOLVED
Hi -
Reading throught this posting I realized I have a very similar issue: I was hoping someone can help me resolve it. I'm getting the error "Select Method of Range Class Failed". My code is assigned to run on the click of a button and I know the code is fine - just can't figure out why this range is not being allowed.
(Also, I took EVERYTHING out except the "bolded" range and it still gave me the error). Anyone???
Option Explicit
Dim currentRow As Range
Dim j As Integer
Private Sub cmdSubmit_Click()
ThisWorkbook.Activate
Sheets("Asian").Activate
Sheets("Asian").Select
Range("A11").Select
If cboCategory.Text = "BLEACH" Then
If ActiveCell.Value = "BLEACH" Then
For j = 0 To 500
'Put current row into current row variable
currentRow = ActiveCell.Row
'Select the current row
Rows(currentRow).Select
'Copy the current row
Selection.Copy
'Go to first empty row in sheets results
Call DoStuffToLast
'Paste currentRow to another worksheet destination
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Activate
Next
Else: ActiveCell.Offset(1, 0).Activate
End If
End If
Sheets("Results").Select
End Sub
Re: Excel VB Run Time Error 1004 - RESOLVED
Welcome to VBForums! :wave:
I've never like using Select and so on - they seem to be disallowed at a whim sometimes! (not to mention the fact that you are testing your luck - the user could select something else while your code is running)
Have you tried being more specific? eg:
VB Code:
Sheets("Asian").Range("A11").Select
..or even this:
VB Code:
ThisWorkBook.Sheets("Asian").Range("A11").Select
Re: Excel VB Run Time Error 1004 - RESOLVED
Hi! Thanks for your response - what I believe was the problem is that I was trying to run the sub straight from the worksheet - when I pasted the code into a module and called the sub from the worksheet then it worked fine. (Public sub). So there must be some scope issues at work here. But there's got to be a way to run .select or .activate etc...from within the current page. Hmmmm....let me know if you think of anything else, otherwise - it's working for me now. Thanks again,
Steve
Quote:
Originally Posted by si_the_geek
Welcome to VBForums! :wave:
I've never like using Select and so on - they seem to be disallowed at a whim sometimes! (not to mention the fact that you are testing your luck - the user could select something else while your code is running)
Have you tried being more specific? eg:
VB Code:
Sheets("Asian").Range("A11").Select
..or even this:
VB Code:
ThisWorkBook.Sheets("Asian").Range("A11").Select
Re: Excel VB Run Time Error 1004 - RESOLVED
Hi,
I'm getting the same run time error - select method of range class failed.
Here's some of the code...
Sub Jen_FV()
Application.DisplayAlerts = False
Workbooks.Open Filename:="Path/FileName.xls"
If WorksheetExists("2- Table Chat Volume By Operat") = True Then
Worksheets("2- Table Chat Volume By Operat").Activate
Worksheets("2- Table Chat Volume By Operat").Select
Range("B13:K37").Select
Selection.Copy
Application.CutCopyMode = False
Windows("Jen.xls").Activate
Worksheets("FVRaw").Range("B40").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
If I try to run the code, the error appears in this line: Worksheets("FVRaw").Range("B40").Select
This error only appears if I run the program from a different module. Please advise.
Re: Excel VB Run Time Error 1004 - RESOLVED
I am new at this macro business. I have a problem similar to Helene, but I tried all the alternatives and nothing works.
I am getting a run time error (1004) in VB for Excel. I have a macro that runs fine through Alt F8. I created a control button and attached the macro. When I use the control button I get run time error 1004 with the message that says "Select method of range class failed."
this is what my macro looks like:
ThisWorkbook.Activate
Sheets("Debt Service").Activate
Sheets("Debt Service").Select
ActiveWindow.SmallScroll Down:=-9
Columns("B:IC").Select
Selection.EntireColumn.Hidden = False
Rows("1:407").Select
Rows("1:407").Select
Selection.EntireRow.Hidden = False
Columns("E:T").Select
Selection.EntireColumn.Hidden = True
Columns("Y:AW").Select
Selection.EntireColumn.Hidden = True
Columns("BC:CA").Select
Selection.EntireColumn.Hidden = True
Columns("CG:DE").Select
Selection.EntireColumn.Hidden = True
Columns("DK:EI").Select
Selection.EntireColumn.Hidden = True
Columns("EO:FM").Select
Selection.EntireColumn.Hidden = True
Columns("FS:GQ").Select
Selection.EntireColumn.Hidden = True
Columns("GW:HU").Select
Selection.EntireColumn.Hidden = True
Range("A3").Select
ActiveWindow.SmallScroll Down:=-6
Rows("8:135").Select
Selection.EntireRow.Hidden = True
Rows("138:226").Select
Selection.EntireRow.Hidden = True
Rows("229:313").Select
Selection.EntireRow.Hidden = True
Rows("317:403").Select
Selection.EntireRow.Hidden = True
Range("B2").Select
End Sub
Re: Excel VB Run Time Error 1004 - RESOLVED
What line of code is highlighted when you get the error?
Re: Excel VB Run Time Error 1004 - RESOLVED
This is the line that is highlighted Selection.EntireColumn.Hidden = False
Re: Excel VB Run Time Error 1004 - RESOLVED
I don't get an error there, but a better way would be to set a worksheet object and then use ranges to hide/unhide, something like this:
Code:
Dim ws As Worksheet
Set ws = ActiveSheet 'or your specific sheet name, better yet
ws.Range("b1:ic1").EntireColumn.Hidden = False