Help with code

[Greg B]

What I am trying to do is have a macro lookup cell b1 from sheets21 to
sheet120 and when it finds the first sheet that this cell is blank to
execute a macro.

I used to use the code below but it is way to long over this many sheets.

If Sheet21.Range("AV3") = "" Then
Sheet21.Select
Else
If Sheet22.Range("AV3") = "" Then
Sheet22.Select
Else
If Sheet23.Range("AV3") = "" Then
Sheet23.Select
Else


etc.

Thanks Greg

 

[Guest]

Greg,

You can do the following, although it has a problem that I'll describe:

dim s as worksheet
for each s in worksheets
if s.range("AV3")="" then
s.select
endif
next s

Now the problem is that this goes through ALL sheets, not just Sheet21
through Sheet120. There are a couple of adjustments you can make.

First, if you can rename your sheets so that you have Sheet021 instead of
Sheet21, then you can use an if statement to find out if the final sheet is
in that range.

If you can't rename them, you could put them into an array and loop through
the array. Or, you could write some code to parse the name and determine if
it's a valid sheet name.

I hope this is of some help.

Art

 

[Bob Phillips]

For Each sh In Activeworkbook.Worksheets
tmp = CLng(Right(sh.Name(Len(sh.Name)-5))
If tmp >= 21 And tmp <= 120 Then
If sh.Range("AV3").Value = "" Then
sh.Select
Exit For
End If
End If
Next sh

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Greg B]

Thanks Bob

For Each sh In Activeworkbook.Worksheets
tmp = CLng(Right(sh.Name(Len(sh.Name)-5))
If tmp >= 21 And tmp <= 120 Then
If sh.Range("AV3").Value = "" Then
sh.Select
Exit For
End If
End If
Next sh

--

HTH

RP
(remove nothere from the email address if mailing direct)