Hi all this is my first time post.
I dabble in some relativly big VBA subs but I am not a programmer. This
has me stumped.
Normally I avoid buttons on spreadsheets I run my macros from toolbar
buttons not sheet buttons. I dont know if it is relevent but it always
seems the small details matter. For this macro I am initiating it from
a sheet button.
In the button code I select a different sheet then select B3, xlright,
xldown I then move into a for each cell in selection. I am getting the
error after the sheet selection, on the line:

Range("B3").Select

Full code:
___________________________________
Private Sub SelectAreas_Click()
ScreenUpdating = False

If Sheets("Benchmarks").Visible = False Then
Sheets("Benchmarks").Visible = True
End If
Sheets("Benchmarks").Select
*_Range(\"B3\").Select_*
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).SelectRange("B3").Select
For Each Cell In Selection
If Cell.Offset(0, 11).Value = "None" Then
Cell.Offset(0, 10).Value = "No"
End If
Next
Range("B2:M245").Select
Selection.autofilter
Selection.autofilter Field:=11, Criteria1:="Yes"
ScreenUpdating = True
End Sub


--
dircur
------------------------------------------------------------------------
dircur's Profile: http://www.excelforum.com/member.php...o&userid=31393
View this thread: http://www.excelforum.com/showthread...hreadid=510910

It seems to work better when preceded with a worksheet object

ActiveSheet.Range("B3").Select


"dircur" wrote:


Hi all this is my first time post.
I dabble in some relativly big VBA subs but I am not a programmer. This
has me stumped.
Normally I avoid buttons on spreadsheets I run my macros from toolbar
buttons not sheet buttons. I dont know if it is relevent but it always
seems the small details matter. For this macro I am initiating it from
a sheet button.
In the button code I select a different sheet then select B3, xlright,
xldown I then move into a for each cell in selection. I am getting the
error after the sheet selection, on the line:

Range("B3").Select

Full code:
___________________________________
Private Sub SelectAreas_Click()
ScreenUpdating = False

If Sheets("Benchmarks").Visible = False Then
Sheets("Benchmarks").Visible = True
End If
Sheets("Benchmarks").Select
*_Range(\"B3\").Select_*
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).SelectRange("B3").Select
For Each Cell In Selection
If Cell.Offset(0, 11).Value = "None" Then
Cell.Offset(0, 10).Value = "No"
End If
Next
Range("B2:M245").Select
Selection.autofilter
Selection.autofilter Field:=11, Criteria1:="Yes"
ScreenUpdating = True
End Sub


--
dircur
------------------------------------------------------------------------
dircur's Profile: http://www.excelforum.com/member.php...o&userid=31393
View this thread: http://www.excelforum.com/showthread...hreadid=510910

The visible property of a worksheet has three possible states. xlvisible,
xlHidden and xlveryHidden. Your if statement could miss unhiding the sheet.
You could simpily do this...

Private Sub SelectAreas_Click()
dim Cell as Range
Dim rngToSearch as range

ScreenUpdating = False

with Sheets("Benchmarks")
.Visible = xlVisible
.Select
set rngToSearch = .range(.Range("B3"), .cells(rows.count, "B").end(xlup))

'Range(Selection, Selection.End(xlToRight)).Select 'I doubt you want to do
this
'Range(Selection, Selection.End(xlDown)).SelectRange("B3").Select
For Each Cell In rngToSearch
If Cell.Offset(0, 11).Value = "None" Then
Cell.Offset(0, 10).Value = "No"
End If
Next
..Range("B2:M245").Select
Selection.autofilter
Selection.autofilter Field:=11, Criteria1:="Yes"
ScreenUpdating = True
End Sub

--
HTH...

Jim Thomlinson


"dircur" wrote:


Hi all this is my first time post.
I dabble in some relativly big VBA subs but I am not a programmer. This
has me stumped.
Normally I avoid buttons on spreadsheets I run my macros from toolbar
buttons not sheet buttons. I dont know if it is relevent but it always
seems the small details matter. For this macro I am initiating it from
a sheet button.
In the button code I select a different sheet then select B3, xlright,
xldown I then move into a for each cell in selection. I am getting the
error after the sheet selection, on the line:

Range("B3").Select

Full code:
___________________________________
Private Sub SelectAreas_Click()
ScreenUpdating = False

If Sheets("Benchmarks").Visible = False Then
Sheets("Benchmarks").Visible = True
End If
Sheets("Benchmarks").Select
*_Range(\"B3\").Select_*
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).SelectRange("B3").Select
For Each Cell In Selection
If Cell.Offset(0, 11).Value = "None" Then
Cell.Offset(0, 10).Value = "No"
End If
Next
Range("B2:M245").Select
Selection.autofilter
Selection.autofilter Field:=11, Criteria1:="Yes"
ScreenUpdating = True
End Sub


--
dircur
------------------------------------------------------------------------
dircur's Profile: http://www.excelforum.com/member.php...o&userid=31393
View this thread: http://www.excelforum.com/showthread...hreadid=510910

I posted late last night and only just now got to work to see your
responses. I think Jims set range method is what I will try first as I
am pretty sure it has something to do with selecting from a different
sheet. I am sure I will get what I need from it but can already see my
next bug. I'll let you know if the range select for applying the data
filter fails.
Does anyone know of a resource that explains the errors?
In my quest for insight on this I found a lot of people getting the
same errors doing some form of a selection. I think if I uderstood the
errror I would better understand why it failed. Meaning I would
understand when it is appropriat and when it is not. I think its
obvious a lot of my code is clipped from recorded macros. These tend to
fail to give you the results you really want. Anyway Thanks Jim I will
repost to say it is solved as soon as I can.


--
dircur
------------------------------------------------------------------------
dircur's Profile: http://www.excelforum.com/member.php...o&userid=31393
View this thread: http://www.excelforum.com/showthread...hreadid=510910

Sorry for taking so long to get back to you. Recording macro's is great to
start to get a handle on the syntax but they are a long way from perfect. To
take your code to the next level you want to get a handle on workbook,
worksheet and range objects. Once you figure these out your code will
drastically improve. It looks like you are already getting a start in that
direction. I can not stress this enough though DECLARE ALL OF YOUR VARIABLES.
This will help to ensure that your code is efficient and with the
intiellisence you will see the properties and methods of the objects.
--
HTH...

Jim Thomlinson


"dircur" wrote:


I posted late last night and only just now got to work to see your
responses. I think Jims set range method is what I will try first as I
am pretty sure it has something to do with selecting from a different
sheet. I am sure I will get what I need from it but can already see my
next bug. I'll let you know if the range select for applying the data
filter fails.
Does anyone know of a resource that explains the errors?
In my quest for insight on this I found a lot of people getting the
same errors doing some form of a selection. I think if I uderstood the
errror I would better understand why it failed. Meaning I would
understand when it is appropriat and when it is not. I think its
obvious a lot of my code is clipped from recorded macros. These tend to
fail to give you the results you really want. Anyway Thanks Jim I will
repost to say it is solved as soon as I can.


--
dircur
------------------------------------------------------------------------
dircur's Profile: http://www.excelforum.com/member.php...o&userid=31393
View this thread: http://www.excelforum.com/showthread...hreadid=510910

Thanks again Jim.
I got through this sub and moving on to the next hopefully the next
persons search will find this thread first.
Thanks again.


--
dircur
------------------------------------------------------------------------
dircur's Profile: http://www.excelforum.com/member.php...o&userid=31393
View this thread: http://www.excelforum.com/showthread...hreadid=510910