Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select failed?
This simple macro hides certain columns, then it's supposed to select
cell B3, but it tells me the select methods failed. Sub HideColTop2AC() Dim rTopCell As Range Dim lColTop As Long Dim WkSht As Worksheet For Each WkSht In ActiveWorkbook.Worksheets If Not Right(WkSht.Name, 7) = "Records" Then Set rTopCell = WkSht.Range("3:3").find("top", LookIn:=xlValues, LookAt:=xlPart) If Not rTopCell Is Nothing Then lColTop = rTopCell.Column WkSht.Range(WkSht.Columns(lColTop), WkSht.Columns("AC")).Hidden = True WkSht.Range("B3").Select<---ERROR End If End If Next End Sub Why is the select failing? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select failed?
The worksheet needs to be active
Sub HideColTop2AC() Dim rTopCell As Range Dim lColTop As Long Dim WkSht As Worksheet For Each WkSht In ActiveWorkbook.Worksheets If Not Right(WkSht.Name, 7) = "Records" Then Set rTopCell = WkSht.Range("3:3").Find("top", LookIn:=xlValues, LookAt:=xlPart) If Not rTopCell Is Nothing Then lColTop = rTopCell.Column WkSht.Range(WkSht.Columns(lColTop), WkSht.Columns("AC")).Hidden = True WkSht.Activate WkSht.Range("B3").Select End If End If Next End Sub -- HTH RP (remove nothere from the email address if mailing direct) "davegb" wrote in message ups.com... This simple macro hides certain columns, then it's supposed to select cell B3, but it tells me the select methods failed. Sub HideColTop2AC() Dim rTopCell As Range Dim lColTop As Long Dim WkSht As Worksheet For Each WkSht In ActiveWorkbook.Worksheets If Not Right(WkSht.Name, 7) = "Records" Then Set rTopCell = WkSht.Range("3:3").find("top", LookIn:=xlValues, LookAt:=xlPart) If Not rTopCell Is Nothing Then lColTop = rTopCell.Column WkSht.Range(WkSht.Columns(lColTop), WkSht.Columns("AC")).Hidden = True WkSht.Range("B3").Select<---ERROR End If End If Next End Sub Why is the select failing? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select failed?
Hi
The sheet is not active on that moment (can't select a cell in a sheet that is not active) WkSht = is the sheet in the loop and that is not the activesheet You can add a WkSht.Select line in the loop -- Regards Ron de Bruin http://www.rondebruin.nl "davegb" wrote in message ups.com... This simple macro hides certain columns, then it's supposed to select cell B3, but it tells me the select methods failed. Sub HideColTop2AC() Dim rTopCell As Range Dim lColTop As Long Dim WkSht As Worksheet For Each WkSht In ActiveWorkbook.Worksheets If Not Right(WkSht.Name, 7) = "Records" Then Set rTopCell = WkSht.Range("3:3").find("top", LookIn:=xlValues, LookAt:=xlPart) If Not rTopCell Is Nothing Then lColTop = rTopCell.Column WkSht.Range(WkSht.Columns(lColTop), WkSht.Columns("AC")).Hidden = True WkSht.Range("B3").Select<---ERROR End If End If Next End Sub Why is the select failing? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select failed?
Try using "WkSht.Range("B3").Activate" instead.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select failed?
The real answer is to remove that line unless you absolutely need B3 to be
selected. The code as written does not do any selecting except for that. -- Regards, Tom Ogilvy "davegb" wrote in message ups.com... This simple macro hides certain columns, then it's supposed to select cell B3, but it tells me the select methods failed. Sub HideColTop2AC() Dim rTopCell As Range Dim lColTop As Long Dim WkSht As Worksheet For Each WkSht In ActiveWorkbook.Worksheets If Not Right(WkSht.Name, 7) = "Records" Then Set rTopCell = WkSht.Range("3:3").find("top", LookIn:=xlValues, LookAt:=xlPart) If Not rTopCell Is Nothing Then lColTop = rTopCell.Column WkSht.Range(WkSht.Columns(lColTop), WkSht.Columns("AC")).Hidden = True WkSht.Range("B3").Select<---ERROR End If End If Next End Sub Why is the select failing? Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select failed?
Thanks for pointing that out, Tom! Turns out, I didn't need it. But I
learned something from it, so it was worth finding out. Tom Ogilvy wrote: The real answer is to remove that line unless you absolutely need B3 to be selected. The code as written does not do any selecting except for that. -- Regards, Tom Ogilvy "davegb" wrote in message ups.com... This simple macro hides certain columns, then it's supposed to select cell B3, but it tells me the select methods failed. Sub HideColTop2AC() Dim rTopCell As Range Dim lColTop As Long Dim WkSht As Worksheet For Each WkSht In ActiveWorkbook.Worksheets If Not Right(WkSht.Name, 7) = "Records" Then Set rTopCell = WkSht.Range("3:3").find("top", LookIn:=xlValues, LookAt:=xlPart) If Not rTopCell Is Nothing Then lColTop = rTopCell.Column WkSht.Range(WkSht.Columns(lColTop), WkSht.Columns("AC")).Hidden = True WkSht.Range("B3").Select<---ERROR End If End If Next End Sub Why is the select failing? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: method 'select' of object_worksheet' failed | Excel Discussion (Misc queries) | |||
What did I do? (Select Method of Range Class Failed ) | Excel Programming | |||
Select method of Range class failed - but why??? | Excel Programming | |||
select method of range class failed | Excel Programming | |||
select method of range class failed | Excel Programming |