Thread: Dynamic Array Problems

Hi all

I'm trying to create a dynamic array so that when a user enters the first sheet name via an input box, excel will know to select the other sheets with ending (2), (3), (4) etc.

Currently this code works only if there are 4 sheets present (as I set this way), anything lower/higher is out of range.

How do I make it so that it will select sheets if there are less than 4 sheets present?

I tried nesting, but I pretty sure this array needs redimming or something. Is ubound a better option?

Any input would be most helpful!

Cheers


Danny

Code:

Option Explicit
Option Base 0
Function SheetExists(SheetName As String) As Boolean
 'returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
   End If
NoSuchSheet:
End Function
Sub testarray()
    Dim x(3)
    Dim strname As String
    Dim ThisBook As Workbook, WkSht As Worksheet
    Set ThisBook = ThisWorkbook
    strname = UCase(InputBox(Prompt:="Please main sheet name.", _
          Title:="User Code Input"))
        If Not SheetExists(strname) Then
             MsgBox strname & " doesn't exist!"
        Else
        x(0) = strname: x(1) = strname & " (2)": x(2) = strname & " (3)": x(3) = strname & " (4)"
        Sheets(x).Select
    End If
    End Sub

This will create an array containing all worksheets named with the prefix user enters.

Code:

Sub testarray()
    Dim x()
    Dim strname As String
    Dim shtTemp As Worksheet
    Dim lngIndex As Long
    
    strname = UCase(InputBox(Prompt:="Please main sheet name.", _
                    Title:="User Code Input"))
    
    For Each shtTemp In ActiveWorkbook.Worksheets
        If Left(UCase(shtTemp.Name), Len(strname)) = strname Then
            ReDim Preserve x(lngIndex)
            x(lngIndex) = shtTemp.Name
            lngIndex = lngIndex + 1
        End If
    Next

    ' reports information stored
    For lngIndex = LBound(x) To UBound(x)
        MsgBox x(lngIndex)
    Next

End Sub

Thanks for you help on this!

The code works fine except this way causes to pick up everything after the string which is what i am trying to avoid

say if I enter 'AH' and it will pick up AH, AH (2), AH (3) and AH (4) along with AHM, AHM (2), etc..

Anyway to get round this?

Cheers

Danny

Code:

Sub Sheetselect()
    Dim x()
    Dim strname As String
    Dim shtTemp As Worksheet
    Dim lngIndex As Long
    
    strname = UCase(InputBox(Prompt:="Please enter sheet code.", _
                    Title:="User Code Input"))
    If strname = vbNullString Then Exit Sub
    
    For Each shtTemp In ActiveWorkbook.Worksheets
        If Left(UCase(shtTemp.Name), Len(strname)) = strname Then
            ReDim Preserve x(lngIndex)
            x(lngIndex) = shtTemp.Name
            lngIndex = lngIndex + 1
        End If
    Next
    
    ' reports information stored
    For lngIndex = LBound(x) To UBound(x)
    Sheets(x).Select
    Next
End Sub

You code add a couple more tests.
Must end with a ) and contain a (
You could even add another test to check text within ( ) is numeric.

Code:

    For Each shtTemp In ActiveWorkbook.Worksheets
        If Left(UCase(shtTemp.Name), Len(strname)) = strname Then
            If Right(shtTemp.Name, 1) = ")" Then
                If InStr(shtTemp.Name, "(") > 0 Then
                    ReDim Preserve x(lngIndex)
                    x(lngIndex) = shtTemp.Name
                    lngIndex = lngIndex + 1
                End If
            End If
        End If
    Next

Yeah I tried to a couple of different variations using left, right, instr but ran into issues with the first sheet. I have managed to affix my original code into this. Luckily it gives me the result I wanted. It's pretty ugly but thought I'd post it up.

Thanks for your help andy. Much appreciated.

Cheers

Danny

Code:

Sub Sheetselect()
  Dim x()
    Dim strname As String
    Dim shtTemp As Worksheet
    Dim lngIndex As Long
   
    strname = UCase(InputBox(Prompt:="Please enter first sheet name.", _
                    Title:="User Code Input"))
    If strname = vbNullString Then Exit Sub
   
    For Each shtTemp In ActiveWorkbook.Worksheets
        If shtTemp.Name = strname Or _
        shtTemp.Name = strname & " (2)" Or _
        shtTemp.Name = strname & " (3)" Or _
        shtTemp.Name = strname & " (4)" Or _
        shtTemp.Name = strname & " (5)" Or _
        shtTemp.Name = strname & " (6)" Then
                    ReDim Preserve x(lngIndex)
                    x(lngIndex) = shtTemp.Name
                    lngIndex = lngIndex + 1
        End If
    Next
     'reports information stored
    For lngIndex = LBound(x) To UBound(x)
    Sheets(x).Select
    Next
End Sub

Thanks for posting your code.

To resolve the issue with the sheet that matches you could add a test to check length of name matches when right closing bracket is not found.