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