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

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" <[email protected]> wrote in message news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Try using "WkSht.Range("B3").Activate" instead.

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >