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 |
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 |