Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with code
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 |
#2
|
|||
|
|||
Help with code
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 "Greg B" wrote: 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 |
#3
|
|||
|
|||
Help with code
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" wrote in message ... 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 |
#4
|
|||
|
|||
Help with code
Thanks Bob
"Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Using other workbooks.. | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) |