Help adding data to an array?

[lleifsen]

Hello,

I am new to using arrays in vba. I am trying to add sheet names to an array once I have declared one. Here is the code I have so far...

I get the following Run-time error '451': Property let procedure not defined and property get procedure did not return an object

...I get this when the if statement is true.

Does anyone have any wisdom to share whith a novice?


Lee

Sub TestArray()
Dim intSheetCount As Integer
Dim SheetNumber As Integer
'Dim intLoopCounter As Integer
'Dim intSelectedCounter As Integer
Dim OwnerName As String ' Is the Report Owner for whom I am selecting reports
Dim SheetName As String 'Active Worksheet Name
Dim OwnerReports() As String 'OwnerReports is my array
intSheetCount = ActiveWorkbook.Sheets.Count - 2
SheetNumber = ActiveSheet.Index
OwnerName = Worksheets("CostCenterOwners").Cells(4, 2)
SheetName = ActiveSheet.Name
ActiveWorkbook.Worksheets("Summary").Activate

Do Until intSheetCount = SheetNumber

If Range("R7") = OwnerName Then 'Range("R7") is the location where the OwnerName is found on each sheet

OwnerReports(SheetName) = ActiveSheet.Name(SheetName) ' If Range("R7") on the active sheet = OwnerName then add the name of the shhet to my array

End If

ActiveSheet.Next.Activate
SheetNumber = ActiveSheet.Index
Namex = ActiveSheet.Name
Loop

Worksheets(OwnerReports).Select

End Sub

 

[kpark91]

Hi, I haven't looked at ur code but to create an array of worksheet names, you can use

Sub sheetray()
    Dim WSNarray$(), i&
    ReDim WSNarray(1 To Sheets.count)
    For i = 1 To Sheets.count
        WSNarray(i) = ThisWorkbook.Sheets(i).Name
    Next
End Sub

http://www.mrexcel.com/forum/showthread.php?t=75445

 

[Jerry Sullivan]

Hi Lee,

Building on kpark's code to create an array with all worksheet names
with your intent to add only those sheets belonging to the Owner.....

Try....

Sub Sheetray2()
    Dim i&, lngMatches&
    Dim WSNarray$() 
    ReDim WSNarray(1 To Sheets.Count)
    Dim OwnerName As String
    OwnerName = Worksheets("CostCenterOwners").Cells(4, 2)
    For i = 1 To Sheets.Count
        With ThisWorkbook.Sheets(i)
            If .Range("R7") = OwnerName Then
                lngMatches = lngMatches + 1
                WSNarray(lngMatches) = .Name
            End If
        End With
    Next
    If lngMatches > 0 Then
        ReDim Preserve WSNarray(1 To lngMatches)
        Worksheets(WSNarray).Select
    Else
        MsgBox "No Matching Reports found for: " & OwnerName
    End If
    Erase WSNarray
End Sub

 

[RoryA]

To address your original code:
This line:

OwnerReports(SheetName) = ActiveSheet.Name(SheetName)

won't work because:
1. The index in an array has to be a number
2. The Name property of a sheet does not take any arguments.
Therefore, it would need to be more like:

OwnerReports(1) = ActiveSheet.Name

but you also need to either resize your array as you go (using Redim Preserve) or specify its size at the start.

 

[lleifsen]

JS411,

Thanks for your help. I have a afew questions:
1st - what does the & and the $ do?
2nd - what does lngMatches equal?

I am trying to learn how to work with arrays, so I am just curious to their function

 

[Jerry Sullivan]

Lee - Glad that helped.

1st - what does the & and the $ do?

That's merely a shorthand way of declaring a variable's data type.

These declarations:

Dim i&, lngMatches&
Dim WSNarray$()

are equivalent to writing:

Dim i as Long
Dim lngMatches as Long
Dim WSNarray() as String

I typically write out the variable types, but was building on the code example posted by kpark in this case.

2nd - what does lngMatches equal?

I used that to count how many sheets are found matching the Owner's name.
That value is needed for two reasons:

1. To test if any matches were found (if not- there is nothing to select)
2. To resize the array to just the number of elements used.