[RESOLVED] Select Method of Range Class failed Error?

Printable View

May 25th, 2005, 10:49 AM
blakemckenna

[RESOLVED] Select Method of Range Class failed Error?

I keep getting the above error message on the following line of code:

Code:

myXLWrkSheet.Columns("A:A").Select

Here is the rest of my code so it makes more sense.

Code:

If aHit > 0 Then Set myXLWrkBook = GetObject(f2) Set myXLWrkSheet = myXLWrkBook.Worksheets(1) numCols = myXLWrkSheet.UsedRange.Columns.count numRows = myXLWrkSheet.UsedRange.rows.count strCol = "A" & numCols myXLWrkSheet.Columns("A:V").Select ////// Error Msg Occurs here \\\\\\\ myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit myXLWrkSheet.Rows("1:1").Select With myXLWrkSheet .Name = "MS Sans Serif" .Size = 8.5 End With myXLWrkSheet.Selection.Font.Bold = True myXLWrkSheet.Columns("A:A").Select myXLWrkSheet.Selection.Insert Shift:=xlToRight myXLWrkSheet.Range("A2").Select myXLWrkSheet.ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[14],RC[1])" myXLWrkSheet.Selection.Copy myXLWrkSheet.Range("B2").Select myXLWrkSheet.Selection.End(xlDown).Select myXLWrkSheet.Selection.End(xlToLeft).Select myXLWrkSheet.Range("A3:A4").Select myXLWrkSheet.Range("A4").Activate myXLWrkSheet.ActiveSheet.Paste Else myXLWrkSheet.Columns("A:V").Select myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit myXLWrkSheet.rows("1:1").Select With myXLWrkSheet .Name = "MS Sans Serif" .Size = 8.5 End With myXLWrkSheet.Range("A" & startrow & ":A" & stoprow).Copy myXLWrkSheet.Columns("A:V").Select myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit myXLWrkSheet.rows("1:1").Select myXLWrkSheet.Selection.Font.Bold = True End If
May 25th, 2005, 11:13 AM
blakemckenna

Re: Select Method of Range Class failed Error?

Does anyone have any ideas???? I really need to get this code out by end-of-day.

Thanks,

Blake
May 25th, 2005, 11:28 AM
RobDog888

Re: Select Method of Range Class failed Error?
Could there be something to do with your specific sheet since it works.

Also, is that sheet the active sheet? Try selecting the sheet first then make your selection.
VB Code:
```
Sheets(1).Select
    Sheets(1).Columns("A:V").Select
```
May 25th, 2005, 11:32 AM
blakemckenna

Re: Select Method of Range Class failed Error?

Rob,

I'll give that a try...

Thanks,

Blake
May 25th, 2005, 11:35 AM
blakemckenna

Re: Select Method of Range Class failed Error?

Rob,

It's not liking any of this code. What am I doing wrong?

Blake
May 25th, 2005, 11:41 AM
RobDog888

Re: Select Method of Range Class failed Error?

Test it in a new workbook. If it works in the new one then you must have applied something to it that it doesnt like.
May 25th, 2005, 11:46 AM
blakemckenna

Re: Select Method of Range Class failed Error?

Rob,

I tried using this code from within a new Access DB. The code accessed the same XLS file and still gave me the same error. Should I try a completely blank worksheet?
May 25th, 2005, 11:49 AM
RobDog888

Re: Select Method of Range Class failed Error?

Yes, and is the workbook visible when you were testing?
May 25th, 2005, 11:51 AM
blakemckenna

Re: Select Method of Range Class failed Error?

No....the workbook is not visible...how do I do that?
May 25th, 2005, 12:15 PM
RobDog888

Re: Select Method of Range Class failed Error?
I'm 99.9% sure that it needs to be visible in order to make any kind of selection.
You can do an ..
VB Code:
```
myXLApp.Visible = True
```
May 25th, 2005, 12:22 PM
blakemckenna

Re: Select Method of Range Class failed Error?

I figured that one out Rob but it still didn't work. I don't understand...this is really weird!!!
May 25th, 2005, 12:45 PM
RobDog888

Re: Select Method of Range Class failed Error?

Does it work in a new workbook? Could it be your installation if it doesnt work in a new visible one.
May 25th, 2005, 12:56 PM
blakemckenna

Re: Select Method of Range Class failed Error?

Rob,

It doesn't work in a new workbook. I can I email you Function. I have certain lines highlighted with colors that might explain things better.

Thanks,

Blake
May 25th, 2005, 02:08 PM
Webtest

Re: Select Method of Range Class failed Error?

Maybe the sheet isn't active? Try: myXLWrkSheet.Activate before selecting the column.
May 25th, 2005, 02:12 PM
Webtest

Re: Select Method of Range Class failed Error?

I'm fairly certain that is the answer ... there is no provision in your code to assure that the sheet is active, and it must be the active sheet to perform selections on it. The selected area does not need to be visible! I learned that in the "School of Hard Knocks"!
May 25th, 2005, 03:08 PM
blakemckenna

Re: Select Method of Range Class failed Error?

Could you please show me the code necessary to acheive this?

Thanks,

Blake
May 25th, 2005, 03:11 PM
RobDog888

Re: Select Method of Range Class failed Error?

Quote:

Originally Posted by Webtest

Maybe the sheet isn't active? Try: myXLWrkSheet.Activate before selecting the column.

Doh! That should take care of the selection issue. I was thinking Visible when I should have thought Active. :(
Also, I got this for the autofit line - myXLWrkSheet.Columns("A:V").AutoFit
May 25th, 2005, 03:13 PM
blakemckenna

Re: Select Method of Range Class failed Error?

I'll give it a try....

thanks!
May 25th, 2005, 03:19 PM
blakemckenna

Re: Select Method of Range Class failed Error?

Still didn't work...

Here's the revised code:

Code:

If fso.FolderExists(filepath) Then Set f = fso.GetFolder(filepath) Set fc = f.SubFolders Set fj = f.Files For Each f2 In fj Set myXLWrkBook = GetObject(f2) Set myXLWrkSheet = myXLWrkBook.Worksheets(1) myXLWrkSheet.Activate /////////// Still not working with this line of code \\\\\\\\\\\\ With myXLWrkSheet With .Rows("1:1").Font .Name = "MS Sans Serif" .Bold = True .Size = 8.5 End With End With aHit = InStr(f2, "DETAIL") If aHit > 0 Then numCols = myXLWrkSheet.UsedRange.Columns.count strCol = "A" & numCols ' myXLWrkSheet.active = True 'myXLWrkSheet.Columns("A:V").Select myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit 'myXLWrkSheet.Rows("1:1").Select myXLWrkSheet.Selection.Font.Bold = True myXLWrkSheet.Columns("A:A").Select myXLWrkSheet.Selection.Insert Shift:=xlToRight myXLWrkSheet.Range("A2").Select myXLWrkSheet.ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[14],RC[1])" myXLWrkSheet.Selection.Copy myXLWrkSheet.Range("B2").Select myXLWrkSheet.Selection.end(xlDown).Select myXLWrkSheet.Selection.end(xlToLeft).Select myXLWrkSheet.Range("A13:A14").Select myXLWrkSheet.Range("A14").Activate myXLWrkSheet.Range(Selection, Selection.end(xlUp)).Select myXLWrkSheet.Range("A2:A14").Select myXLWrkSheet.Range("A14").Activate myXLWrkSheet.ActiveSheet.Paste End If myXLWrkBook.Save myXLWrkBook.Close Set myXLWrkSheet = Nothing Set myXLWrkBook = Nothing aHit = 0 Next End If
May 25th, 2005, 03:41 PM
RobDog888

Re: Select Method of Range Class failed Error?
VB Code:
```
'It should be ...
myXLWrkSheet.Activate
```
May 25th, 2005, 03:43 PM
blakemckenna

Re: Select Method of Range Class failed Error?

I tried that Rob and it still doesn't work. I still get the same error messages. I don't know what to do...
May 25th, 2005, 04:11 PM
RobDog888

Re: Select Method of Range Class failed Error?
I think it may have something to do with the way your instanciating the object.
VB Code:
```
Set myXLWrkBook = GetObject(f2)
'Try instead
Set myXLApp = CreateObject("Excel.Application")
Set myXLWrkBook = myXLApp.Workbooks.Open(f2)
'...
'...
```
May 25th, 2005, 04:28 PM
Webtest

Re: Select Method of Range Class failed Error?

For heaven's sake, put "Option Strict" as the very top line of your module. If it breaks anything, that is GOOD, because broken things were being hidden!
May 25th, 2005, 04:57 PM
blakemckenna

Re: Select Method of Range Class failed Error?

I get an "Expected: Base or Compare or Explicit or Private" error when I try to insert "Option Strict".

Once again...here's what I have so far....

Code:

For Each f2 In fj Set myXLApp = CreateObject("Excel.Application") Set myXLWrkBook = myXLApp.Workbooks.Open(f2) Set myXLWrkSheet = myXLWrkBook.Worksheets(1) myXLWrkBook.Application.Windows(1).Visible = True With myXLWrkSheet With .Rows("1:1").Font .Name = "MS Sans Serif" .Bold = True .Size = 8.5 End With End With aHit = InStr(f2, "DETAIL") If aHit > 0 Then numCols = myXLWrkSheet.UsedRange.Columns.count strCol = "A" & numCols myXLWrkSheet.Columns("A:V").EntireColumn.AutoFit myXLWrkSheet.Columns("A:V").Select myXLWrkSheet.Rows("1:1").Select ' myXLWrkSheet.Selection.Font.Bold = True myXLWrkSheet.Columns("A:A").Select ' myXLApp.myXLWrkSheet.Selection.Insert Shift:=xlToRight myXLWrkSheet.Range("A2").Select ' myXLWrkSheet.ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[14],RC[1])" ' myXLWrkSheet.Selection.Copy myXLWrkSheet.Range("B2").Select ' myXLWrkSheet.Selection.End(xlDown).Select ' myXLWrkSheet.Selection.End(xlToLeft).Select myXLWrkSheet.Range("A13:A14").Select myXLWrkSheet.Range("A14").Activate ' myXLWrkSheet.Range(Selection, Selection.End(xlUp)).Select myXLWrkSheet.Range("A2:A14").Select myXLWrkSheet.Range("A14").Activate ' myXLWrkSheet.ActiveSheet.Paste End If myXLWrkBook.Save myXLWrkBook.Close Set myXLWrkSheet = Nothing Set myXLWrkBook = Nothing Next

The only thing not working now are the lines of code that are commented out.

I am getting closer!!!!!

Thanks to all who are helping!
May 25th, 2005, 05:01 PM
RobDog888

Re: Select Method of Range Class failed Error?

You can not use "Option Strict" in VB6/VBA. Its only for .NET
You may be thinking of "Option Explicit" for VB6/VBA.

So it was the GetObject that was causing some of the issues. :thumb:
May 25th, 2005, 05:10 PM
blakemckenna

Re: Select Method of Range Class failed Error?

Who knows....all I know is that it didn't fix it completely. I just don't know VBA well enough to figure this out on my own.

I'll stick with straight VB anyday....HAHA
May 25th, 2005, 05:25 PM
RobDog888

Re: Select Method of Range Class failed Error?
Here's the fix...
VB Code:
```
Selection.Font.Bold = True
'Repeat syntax for other .Selection lines of code
```
The Selection object is an Application level object and not a sheet level object.
May 26th, 2005, 07:00 AM
Webtest

Re: Select Method of Range Class failed Error?

Regarding the "Option Explicit": My humble apologies ... I got my forums mixed up. I normally lurk on the .Net forum, and I was in a hurry to get home last night.
May 26th, 2005, 11:04 AM
RobDog888

Re: Select Method of Range Class failed Error?

No worries. :) I have had similar things happen when switching between VB6/VB.NET/VBA/... :lol:

Blake, I will be out for a few hours, but I think my last post should take care of the rest of the errors.
May 26th, 2005, 12:32 PM
blakemckenna

Re: Select Method of Range Class failed Error?

Ok guys,

I've almost got this thing working. I have also been talking to a VBA guru from another forum. They almost have this app doing what I want it to do. However, the app is creating 2 hyperlinks in some files when it should only be creating 1. I believe this is where variables come into play using the "Range" object but I don't know how to program it. I also need to hide the target column of the hyperlink as well as the "Friendly Name" column (which will always be in Column 2).

Anyway, here is the current code that I have to date.

Code:

For Each f2 In fj Set myXLApp = CreateObject("Excel.Application") Set myXLWrkBook = myXLApp.Workbooks.Open(f2) Set myXLWrkSheet = myXLWrkBook.Worksheets(1) myXLWrkBook.Application.Windows(1).Visible = True With myXLWrkSheet .Columns("A:V").AutoFit With .Rows("1:1").Font .Name = "MS Sans Serif" .Bold = True .Size = 8.5 End With End With If f2 Like "*DETAIL*" Then numCols = myXLWrkSheet.UsedRange.Columns.count With myXLWrkSheet .Rows("1:1").Font.Bold = True .Columns("A").Insert .Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).FormulaR1C1 _ = "=HYPERLINK(RC[14],RC[1])" .Columns("A:V").AutoFit .Selection.EntireColumn.Hidden = True End With End If myXLWrkBook.Close True Set myXLWrkSheet = Nothing Set myXLWrkBook = Nothing Next

If anyone can shed light on this...I'd be grateful. By the way....the responses from this orher forum....I have to pay for. So...needless to say, free help is much better.

Thanks again for your help,

Blake
May 26th, 2005, 01:56 PM
Webtest

Re: Select Method of Range Class failed Error?

You are hiding a column based on a selected range (highlighted on the sheet):

myXLWrkSheet.Selection.EntireColumn.Hidden = True

... but you have never set the selection (in the code that is shown). Either be sure to select the range you want to hide:

myXLWrkSheet.<Range object>.Select

OR - hide a range object instead of a selected range:

myXLWrkSheet.<Range object>.EntireColumn.Hidden = True

Immediately before the following line:
.Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).FormulaR1C1 _
= "=HYPERLINK(RC[14],RC[1])"

Put a temporary diagnostic popup and see if it makes any sense:

Code:

'TEST TEST TEST TEST myXLWrkSheet.Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).Select MsgBox (myXLWrkSheet.Range("A2", .Range("B65536").End(xlUp).Offset(0, -1)).Address) 'END TEST
May 26th, 2005, 01:59 PM
Webtest

Re: Select Method of Range Class failed Error?

... and what on Earth are you trying to accomplish with this?

myXLWrkSheet.Range("A2", .Range("B65536").End(xlUp).Offset(0, -1))
May 26th, 2005, 02:22 PM
blakemckenna

Re: Select Method of Range Class failed Error?

Web,

I have no idea exactly what this code is doing. All I know is that I need to create a hyperlink in Column 1, assign it the same heading as in Column 2, and then hide the Target Column and "Friendly Name" Column.

The "Friendly Name" column will always be in Column 2, however, the Target Column varies in it's location.

Can ya help???

God Bless ya,

Blake
May 26th, 2005, 02:28 PM
Webtest

Re: Select Method of Range Class failed Error?

You need to hire a programmer. Sorry - I've got my own work to do right now.
May 26th, 2005, 02:45 PM
blakemckenna

Re: Select Method of Range Class failed Error?

Ok bro, I understand....thanks for your help and input.

Have a great day!!!

Blake
Oct 4th, 2012, 10:20 AM
GeordieSi

Re: Select Method of Range Class failed Error?

Realise this is a pretty old thread, but its the first hit on Google so thought I'd share my solution in case it helps anyone else...

The issue here may have been that the Macro was defined as private sub (should just be plain old sub), or it was written in an object other than Worksheet1.

'Global' macros should be placed in a module (this was my issue... only took about 3 hours to figure it out :()
Oct 8th, 2012, 09:30 AM
blakemckenna

Re: Select Method of Range Class failed Error?

This thread is so old I don't even remember opening it. I will close this immediately...

All times are GMT -5. The time now is 05:20 AM.