Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
Interesting, after playing with both your solutions offered, after I made the
correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
But if you really want the first worksheet with the name that matched that
string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
I see that the last one is the shortest most efficient code so will use it.
To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
I think I'd continue to use the loop, but then use a Like comparison.
The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
Correct, the ##### represent unique permit numbers for sets of worksheets.
When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
Maybe...
If LCase(Sheets(i).Name) Like "#####-sum*" _ or LCase(Sheets(i).Name) Like "#####sum" Then If that doesn't help, I think it's time for you to post your current code. owlnevada wrote: Correct, the ##### represent unique permit numbers for sets of worksheets. When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
Here it is. I am trying to array the page numbers when they are 2 or
greater, get the value of the page number I'm looking for from the input box, then select all those that have the page number I want. I only need to look for the digits 1-19 after the word "#####sum-". I now have a syntax error on the line beginning with If LCase. . . Seems like we would maybe have two arrays, one called sheetnames and one for page numbers? This is where I'm lost. Many Thanks once again. Private Sub SelectAllSumOnePageNum() 'try to code once to select any page # _using an InputBox Dim sheetnames As Variant Dim PNum As Integer SumCount = 0 InputBox "What is the page number (=2) you want to select?, Page Number Selection ,2" For i = 1 To sheets.count If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum), "sum-""(PNum)", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve sheetnames(1 To SumCount) sheetnames(SumCount) = sheets(i).Name(PNum) End If Next End Sub "Dave Peterson" wrote: Maybe... If LCase(Sheets(i).Name) Like "#####-sum*" _ or LCase(Sheets(i).Name) Like "#####sum" Then If that doesn't help, I think it's time for you to post your current code. owlnevada wrote: Correct, the ##### represent unique permit numbers for sets of worksheets. When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
Just to make sure...
There is no dash before the word "sum". It's 5 digits followed by "sum" followed by a hyphen, followed by that page number, right? And you want to select all the sheets that match that naming convention? Option Explicit Sub SelectAllSumOnePageNum() 'try to code once to select any page # using an InputBox Dim i As Long Dim Sheetnames() As String Dim SumCount As Long Dim PNum As Long SumCount = 0 PNum = Application.InputBox _ (Prompt:="What is the page number (=2) you want to select?", _ Title:="Page Number Selection", Default:=2, Type:=1) If PNum < 2 Then Exit Sub End If For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like LCase("#####sum-" & PNum) Then SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets match that pattern!" Exit Sub End If Sheets(Sheetnames).Select End Sub I used Application.inputbox with type:=1 so that I didn't have to verify that the user's entry was numeric. If I had used VBA's inputbox, then I'd have to check. owlnevada wrote: Here it is. I am trying to array the page numbers when they are 2 or greater, get the value of the page number I'm looking for from the input box, then select all those that have the page number I want. I only need to look for the digits 1-19 after the word "#####sum-". I now have a syntax error on the line beginning with If LCase. . . Seems like we would maybe have two arrays, one called sheetnames and one for page numbers? This is where I'm lost. Many Thanks once again. Private Sub SelectAllSumOnePageNum() 'try to code once to select any page # _using an InputBox Dim sheetnames As Variant Dim PNum As Integer SumCount = 0 InputBox "What is the page number (=2) you want to select?, Page Number Selection ,2" For i = 1 To sheets.count If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum), "sum-""(PNum)", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve sheetnames(1 To SumCount) sheetnames(SumCount) = sheets(i).Name(PNum) End If Next End Sub "Dave Peterson" wrote: Maybe... If LCase(Sheets(i).Name) Like "#####-sum*" _ or LCase(Sheets(i).Name) Like "#####sum" Then If that doesn't help, I think it's time for you to post your current code. owlnevada wrote: Correct, the ##### represent unique permit numbers for sets of worksheets. When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
Right on both questions. Page one name is just "#####sum" with no dash or
number 1. I tried this on a test file with ". . .sum-2" at index position 2,9, and 12 in a 14 sheet workbook. . . curiously, it selected indexed sheets 9 and 12 just fine but didn't select the one at position 2. . . any idea on that? "Dave Peterson" wrote: Just to make sure... There is no dash before the word "sum". It's 5 digits followed by "sum" followed by a hyphen, followed by that page number, right? And you want to select all the sheets that match that naming convention? Option Explicit Sub SelectAllSumOnePageNum() 'try to code once to select any page # using an InputBox Dim i As Long Dim Sheetnames() As String Dim SumCount As Long Dim PNum As Long SumCount = 0 PNum = Application.InputBox _ (Prompt:="What is the page number (=2) you want to select?", _ Title:="Page Number Selection", Default:=2, Type:=1) If PNum < 2 Then Exit Sub End If For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like LCase("#####sum-" & PNum) Then SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets match that pattern!" Exit Sub End If Sheets(Sheetnames).Select End Sub I used Application.inputbox with type:=1 so that I didn't have to verify that the user's entry was numeric. If I had used VBA's inputbox, then I'd have to check. owlnevada wrote: Here it is. I am trying to array the page numbers when they are 2 or greater, get the value of the page number I'm looking for from the input box, then select all those that have the page number I want. I only need to look for the digits 1-19 after the word "#####sum-". I now have a syntax error on the line beginning with If LCase. . . Seems like we would maybe have two arrays, one called sheetnames and one for page numbers? This is where I'm lost. Many Thanks once again. Private Sub SelectAllSumOnePageNum() 'try to code once to select any page # _using an InputBox Dim sheetnames As Variant Dim PNum As Integer SumCount = 0 InputBox "What is the page number (=2) you want to select?, Page Number Selection ,2" For i = 1 To sheets.count If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum), "sum-""(PNum)", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve sheetnames(1 To SumCount) sheetnames(SumCount) = sheets(i).Name(PNum) End If Next End Sub "Dave Peterson" wrote: Maybe... If LCase(Sheets(i).Name) Like "#####-sum*" _ or LCase(Sheets(i).Name) Like "#####sum" Then If that doesn't help, I think it's time for you to post your current code. owlnevada wrote: Correct, the ##### represent unique permit numbers for sets of worksheets. When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
Share the exact names of those 3 sheets.
Share what you typed into the inputbox. Look out for leading and trailing spaces in those worksheet names, too. And there's a difference between: 12345sum-0002 and 12345sum-2 owlnevada wrote: Right on both questions. Page one name is just "#####sum" with no dash or number 1. I tried this on a test file with ". . .sum-2" at index position 2,9, and 12 in a 14 sheet workbook. . . curiously, it selected indexed sheets 9 and 12 just fine but didn't select the one at position 2. . . any idea on that? "Dave Peterson" wrote: Just to make sure... There is no dash before the word "sum". It's 5 digits followed by "sum" followed by a hyphen, followed by that page number, right? And you want to select all the sheets that match that naming convention? Option Explicit Sub SelectAllSumOnePageNum() 'try to code once to select any page # using an InputBox Dim i As Long Dim Sheetnames() As String Dim SumCount As Long Dim PNum As Long SumCount = 0 PNum = Application.InputBox _ (Prompt:="What is the page number (=2) you want to select?", _ Title:="Page Number Selection", Default:=2, Type:=1) If PNum < 2 Then Exit Sub End If For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like LCase("#####sum-" & PNum) Then SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets match that pattern!" Exit Sub End If Sheets(Sheetnames).Select End Sub I used Application.inputbox with type:=1 so that I didn't have to verify that the user's entry was numeric. If I had used VBA's inputbox, then I'd have to check. owlnevada wrote: Here it is. I am trying to array the page numbers when they are 2 or greater, get the value of the page number I'm looking for from the input box, then select all those that have the page number I want. I only need to look for the digits 1-19 after the word "#####sum-". I now have a syntax error on the line beginning with If LCase. . . Seems like we would maybe have two arrays, one called sheetnames and one for page numbers? This is where I'm lost. Many Thanks once again. Private Sub SelectAllSumOnePageNum() 'try to code once to select any page # _using an InputBox Dim sheetnames As Variant Dim PNum As Integer SumCount = 0 InputBox "What is the page number (=2) you want to select?, Page Number Selection ,2" For i = 1 To sheets.count If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum), "sum-""(PNum)", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve sheetnames(1 To SumCount) sheetnames(SumCount) = sheets(i).Name(PNum) End If Next End Sub "Dave Peterson" wrote: Maybe... If LCase(Sheets(i).Name) Like "#####-sum*" _ or LCase(Sheets(i).Name) Like "#####sum" Then If that doesn't help, I think it's time for you to post your current code. owlnevada wrote: Correct, the ##### represent unique permit numbers for sets of worksheets. When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
I accepted the default 2 by clicking OK. The sheets are 3530sum-2,
72823sum-2, and 75296sum-2, or indexed as in the order stated previously. I checked the exact names by using my macro which does that, called ingeniously "Sheetnames" and ran it again with the same results. . . selecting the last two only. "Dave Peterson" wrote: Share the exact names of those 3 sheets. Share what you typed into the inputbox. Look out for leading and trailing spaces in those worksheet names, too. And there's a difference between: 12345sum-0002 and 12345sum-2 owlnevada wrote: Right on both questions. Page one name is just "#####sum" with no dash or number 1. I tried this on a test file with ". . .sum-2" at index position 2,9, and 12 in a 14 sheet workbook. . . curiously, it selected indexed sheets 9 and 12 just fine but didn't select the one at position 2. . . any idea on that? "Dave Peterson" wrote: Just to make sure... There is no dash before the word "sum". It's 5 digits followed by "sum" followed by a hyphen, followed by that page number, right? And you want to select all the sheets that match that naming convention? Option Explicit Sub SelectAllSumOnePageNum() 'try to code once to select any page # using an InputBox Dim i As Long Dim Sheetnames() As String Dim SumCount As Long Dim PNum As Long SumCount = 0 PNum = Application.InputBox _ (Prompt:="What is the page number (=2) you want to select?", _ Title:="Page Number Selection", Default:=2, Type:=1) If PNum < 2 Then Exit Sub End If For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like LCase("#####sum-" & PNum) Then SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets match that pattern!" Exit Sub End If Sheets(Sheetnames).Select End Sub I used Application.inputbox with type:=1 so that I didn't have to verify that the user's entry was numeric. If I had used VBA's inputbox, then I'd have to check. owlnevada wrote: Here it is. I am trying to array the page numbers when they are 2 or greater, get the value of the page number I'm looking for from the input box, then select all those that have the page number I want. I only need to look for the digits 1-19 after the word "#####sum-". I now have a syntax error on the line beginning with If LCase. . . Seems like we would maybe have two arrays, one called sheetnames and one for page numbers? This is where I'm lost. Many Thanks once again. Private Sub SelectAllSumOnePageNum() 'try to code once to select any page # _using an InputBox Dim sheetnames As Variant Dim PNum As Integer SumCount = 0 InputBox "What is the page number (=2) you want to select?, Page Number Selection ,2" For i = 1 To sheets.count If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum), "sum-""(PNum)", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve sheetnames(1 To SumCount) sheetnames(SumCount) = sheets(i).Name(PNum) End If Next End Sub "Dave Peterson" wrote: Maybe... If LCase(Sheets(i).Name) Like "#####-sum*" _ or LCase(Sheets(i).Name) Like "#####sum" Then If that doesn't help, I think it's time for you to post your current code. owlnevada wrote: Correct, the ##### represent unique permit numbers for sets of worksheets. When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
I see the problems now at the LCase line. . . so I removed one of the #, ran
it again, and of course it selected only the 3530sum-2 and not the other two. .. . so we need to get all the sums that can have from 1 to 5 digits . . . hoping using a wildcard for those # will work? "owlnevada" wrote: I accepted the default 2 by clicking OK. The sheets are 3530sum-2, 72823sum-2, and 75296sum-2, or indexed as in the order stated previously. I checked the exact names by using my macro which does that, called ingeniously "Sheetnames" and ran it again with the same results. . . selecting the last two only. "Dave Peterson" wrote: Share the exact names of those 3 sheets. Share what you typed into the inputbox. Look out for leading and trailing spaces in those worksheet names, too. And there's a difference between: 12345sum-0002 and 12345sum-2 owlnevada wrote: Right on both questions. Page one name is just "#####sum" with no dash or number 1. I tried this on a test file with ". . .sum-2" at index position 2,9, and 12 in a 14 sheet workbook. . . curiously, it selected indexed sheets 9 and 12 just fine but didn't select the one at position 2. . . any idea on that? "Dave Peterson" wrote: Just to make sure... There is no dash before the word "sum". It's 5 digits followed by "sum" followed by a hyphen, followed by that page number, right? And you want to select all the sheets that match that naming convention? Option Explicit Sub SelectAllSumOnePageNum() 'try to code once to select any page # using an InputBox Dim i As Long Dim Sheetnames() As String Dim SumCount As Long Dim PNum As Long SumCount = 0 PNum = Application.InputBox _ (Prompt:="What is the page number (=2) you want to select?", _ Title:="Page Number Selection", Default:=2, Type:=1) If PNum < 2 Then Exit Sub End If For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like LCase("#####sum-" & PNum) Then SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets match that pattern!" Exit Sub End If Sheets(Sheetnames).Select End Sub I used Application.inputbox with type:=1 so that I didn't have to verify that the user's entry was numeric. If I had used VBA's inputbox, then I'd have to check. owlnevada wrote: Here it is. I am trying to array the page numbers when they are 2 or greater, get the value of the page number I'm looking for from the input box, then select all those that have the page number I want. I only need to look for the digits 1-19 after the word "#####sum-". I now have a syntax error on the line beginning with If LCase. . . Seems like we would maybe have two arrays, one called sheetnames and one for page numbers? This is where I'm lost. Many Thanks once again. Private Sub SelectAllSumOnePageNum() 'try to code once to select any page # _using an InputBox Dim sheetnames As Variant Dim PNum As Integer SumCount = 0 InputBox "What is the page number (=2) you want to select?, Page Number Selection ,2" For i = 1 To sheets.count If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum), "sum-""(PNum)", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve sheetnames(1 To SumCount) sheetnames(SumCount) = sheets(i).Name(PNum) End If Next End Sub "Dave Peterson" wrote: Maybe... If LCase(Sheets(i).Name) Like "#####-sum*" _ or LCase(Sheets(i).Name) Like "#####sum" Then If that doesn't help, I think it's time for you to post your current code. owlnevada wrote: Correct, the ##### represent unique permit numbers for sets of worksheets. When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case by Sheetname Property-String vs Array
How about just looking for (anything)Sum-(pagenumber)?
If LCase(Sheets(i).Name) Like LCase("*sum-" & PNum) owlnevada wrote: I see the problems now at the LCase line. . . so I removed one of the #, ran it again, and of course it selected only the 3530sum-2 and not the other two. . . so we need to get all the sums that can have from 1 to 5 digits . . . hoping using a wildcard for those # will work? "owlnevada" wrote: I accepted the default 2 by clicking OK. The sheets are 3530sum-2, 72823sum-2, and 75296sum-2, or indexed as in the order stated previously. I checked the exact names by using my macro which does that, called ingeniously "Sheetnames" and ran it again with the same results. . . selecting the last two only. "Dave Peterson" wrote: Share the exact names of those 3 sheets. Share what you typed into the inputbox. Look out for leading and trailing spaces in those worksheet names, too. And there's a difference between: 12345sum-0002 and 12345sum-2 owlnevada wrote: Right on both questions. Page one name is just "#####sum" with no dash or number 1. I tried this on a test file with ". . .sum-2" at index position 2,9, and 12 in a 14 sheet workbook. . . curiously, it selected indexed sheets 9 and 12 just fine but didn't select the one at position 2. . . any idea on that? "Dave Peterson" wrote: Just to make sure... There is no dash before the word "sum". It's 5 digits followed by "sum" followed by a hyphen, followed by that page number, right? And you want to select all the sheets that match that naming convention? Option Explicit Sub SelectAllSumOnePageNum() 'try to code once to select any page # using an InputBox Dim i As Long Dim Sheetnames() As String Dim SumCount As Long Dim PNum As Long SumCount = 0 PNum = Application.InputBox _ (Prompt:="What is the page number (=2) you want to select?", _ Title:="Page Number Selection", Default:=2, Type:=1) If PNum < 2 Then Exit Sub End If For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like LCase("#####sum-" & PNum) Then SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets match that pattern!" Exit Sub End If Sheets(Sheetnames).Select End Sub I used Application.inputbox with type:=1 so that I didn't have to verify that the user's entry was numeric. If I had used VBA's inputbox, then I'd have to check. owlnevada wrote: Here it is. I am trying to array the page numbers when they are 2 or greater, get the value of the page number I'm looking for from the input box, then select all those that have the page number I want. I only need to look for the digits 1-19 after the word "#####sum-". I now have a syntax error on the line beginning with If LCase. . . Seems like we would maybe have two arrays, one called sheetnames and one for page numbers? This is where I'm lost. Many Thanks once again. Private Sub SelectAllSumOnePageNum() 'try to code once to select any page # _using an InputBox Dim sheetnames As Variant Dim PNum As Integer SumCount = 0 InputBox "What is the page number (=2) you want to select?, Page Number Selection ,2" For i = 1 To sheets.count If LCase(sheets(i).Name "(PNum)" Like InStr(1, sheets(i).Name(PNum), "sum-""(PNum)", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve sheetnames(1 To SumCount) sheetnames(SumCount) = sheets(i).Name(PNum) End If Next End Sub "Dave Peterson" wrote: Maybe... If LCase(Sheets(i).Name) Like "#####-sum*" _ or LCase(Sheets(i).Name) Like "#####sum" Then If that doesn't help, I think it's time for you to post your current code. owlnevada wrote: Correct, the ##### represent unique permit numbers for sets of worksheets. When the tabname includes "sum-2", "sum-3", those are the sheets I want to select as a group for further work. "Sum-*" is for multiple pages when more than a single page. If just a one page sum, the its just "#####sum" and can be selected easily with existing code. I ran this code without errors but it makes no selection. What else needs to be coded? I removed the .name as before and it made no difference. I would like an input box to ask for the page number, then continue to select them, so that I need to code it only once. My need comes from thousands of files that have combinations of single and mulitiple pages of sheets with "sum*" in the name and need to be selected easily for further work as a group. "Dave Peterson" wrote: I think I'd continue to use the loop, but then use a Like comparison. The code is essentially the same... SumCount = 0 For i = 1 To Sheets.Count If LCase(Sheets(i).Name) Like "#####-sum*" Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i I'm assuming that ##### represents 5 digits, right? or maybe If LCase(Sheets(i).Name) Like "#####-sum-2" Then I'm not sure what the pattern actually is. owlnevada wrote: I see that the last one is the shortest most efficient code so will use it. To get back to my first post, in my approach using the Select Case, in order to bolster my understanding to accomplish my tasks, is there an easy modification to that that you can offer or is it just a poor way to do the same thing? If there is, then I might find that useful so that I could say modify that to select a certain group of sheets that have some common characters like "#####-sum-2" scattered thruout the workbook? "Dave Peterson" wrote: But if you really want the first worksheet with the name that matched that string, then there isn't a real good reason to keep looking. owlnevada wrote: Interesting, after playing with both your solutions offered, after I made the correction to delete the .name property as Rick suggested below and found it worked perfectly, I went back to the first one and discovered that when I posted just the very last line of your first solution with the Lbound statement to my code, it worked perfectly also. Thought you should know. Thanks again to both of you! It's been a great help. "Dave Peterson" wrote: I'm kind of confused at OneIWant is, but maybe this'll help: Option Explicit Sub Select1stSummary() Dim i As Long Dim Sheetnames() As String Dim SumCount As Long SumCount = 0 For i = 1 To Sheets.Count If InStr(1, Sheets(i).Name, "sum", vbTextCompare) 0 Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array SumCount = SumCount + 1 'so we can resize the array to the number of sheets ReDim Preserve Sheetnames(1 To SumCount) Sheetnames(SumCount) = Sheets(i).Name End If Next i If SumCount = 0 Then MsgBox "No sheets with Sum in the name!" Exit Sub End If Sheets(Sheetnames).Select Sheets(Sheetnames(UBound(Sheetnames))).Activate End Sub owlnevada wrote: This code runs without error but is not selecting the first sheet in the index that I want which selects only those sheetnames that have "sum" in the character string of tabnames. The other worksheets would have something other than "sum" as part of the name. Instead, it ends up with the highest index numbered sheet as the active sheet and I want it to be the first or index(1) in the string. It is not the same (1) for the entire workbook, usually the 2nd or higher indexed sheet and the first part of the routine narrows that down to a subset of worksheets within the workbook. Not sure if the problem is that it is a string and needs to be an array or if the Select Case is the wrong approach. Any help is most appreciated! Sub Select1stSummary() 'Selects All Summarys Macro groups summary sheets for further work as before Dim i As Integer Dim Sheetnames() As String Dim Count As Integer Dim ws As Sheets Dim OneIwant As String sumCount = 0 For i = 1 To Sheets.Count If InStr(Sheets(i).Name, "sum") Then ' got a handle on a summary sheet - ' Save the name of the sheet in an array Count = Count + 1 ' we need to count the sheets ReDim Preserve Sheetnames(1 To 5) ' so we can resize the array to the number of sheets Sheetnames(Count) = Sheets(i).Name End If Next i OneIwant = Sheetnames(Count) ' Now all the names of the sheets that we want are in an array, select them all at once ' On Error GoTo ErrorHandler 'Resume Next ' Exit Sub On Error Resume Next ' With Sheets(Count) Select Case Sheets(i) Case 0 Case (i) = 1 Sheets(1).Select End Select 'End With End Sub -- Dave Peterson -- Dave Peterson -- -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Automatically update SheetName in workbook sub if SheetName changes | Excel Discussion (Misc queries) | |||
Select Case Not Returning Correct String | Excel Programming | |||
Get Property value of class instance by passing string property name | Excel Programming |