how would i message out the index o 1

[Luis939]

how would i message out the index of an array in my for loop

dim myarray()
:
: 'inserting values in indices
:
for each item in myarray
msgbox item. ?????????

that last line is where i dont know how to output the index

thanks!

 

[Zathras]

[blue]
Im a little confused as to why you added .name in
MasterCollection.Add oCollection, .Name[/color]

So that each collection can be found by name (Key)
[blue]
and what this does
Set oCollection = MasterCollection(.Name)[/color]

Provides access to the items in the collection for a given worksheet (keyed by name)
[blue]
and why did you write
With .Range(oCollection(i))
how does it know that you were talking about a range??
[/color]
Simply because it is the address of each cell (range) that was stored in the collection as it was found (by .Find and .FindNext) It seemed the easiest conceptually, rather than adding a range object to the collection. (Which would have meant more memory to free up at the end of the operation.)
[blue]
Thanks!
[/color]
You're welcome.
[blue]
...just wanted to save the time of referencing all worksheets again...
[/color]
Why do you think this might be a problem? How many sheets do you have? It would be interesting to know what the scenario really is that you are working with.

 

[Luis939]

Ok it looks like i finally have my solution, but i defintely would like you to view it and give me suggestions as to whether this is the best way to do it or how to improve it,
first i probably should have given you a better example of the worksheets i was working with but basically all my code was taken from Zanthras' code, thanks buddy! here goes
I decided first to make a collection for each valid sheet where the first index, was the name of the worksheet, the remaining indices would contain my ranges, then store that collection into a master collection and use the first index of each collection as my workbook to get the rest of the data i was working with:


Sub mycollection()

Dim collection1 As New Collection
Dim mastercollection As New Collection
Dim q As Byte

On Error Resume Next

myarray = Array("interstate", "offshore","intrastate&quot

For i = 1 To ThisWorkbook.sheets.Count

Set myrange1 = ThisWorkbook.sheets(i).UsedRange.Find(what:="Usage&quot

If Not myrange1 Is Nothing Then
collection1.Add sheets(i)

For Each Index In myarray
Set MyRange = ThisWorkbook.sheets(i).UsedRange.Find(what:=Index)

If Not MyRange Is Nothing Then collection1.Add MyRange
Next Index

mastercollection.Add collection1
Else
End If
Set collection1 = Nothing

Next i

For Each Item In mastercollection

For q = 2 To Item.Count

msgbox item(1) & " " & item(q)

Next q
Next Item

End Sub


this way i can use item(1) in my forloop whenever i needed to reference the worksheets in my collections

One question, the line

Set collection1 = Nothing

without that line my code didnt work, it just grouped everything together, why is this line useful? does it segment it appropriately, if it does then how, thats the part confusing me

Other than that, any opinions, comments, objectives, please i'm always open to learn more, thanks!!!

 

[Zathras]

1. It is usually easier to see what is going on if you use a consistent indentation scheme.

2. You have made it a little more difficult for yourself by storing objects in the collections instead of strings, but it can be done that way if you insist. (But see the changes required in the MsgBox line.)

3. It is always a good idea to use

 Option Explicit

as the first line in any module. That forces you to DIM each and every variable and object you are using. Helps to find spelling errors.

4. Since you are not using .FindNext anywhere, I assume that there is always going to be only one instance of "intrastate", "offshore" or "interstate" in any one sheet. Also it will always only be typed in all lower case in the worksheets since you are doing a simple compare.

5. Not sure why q is defined as a Byte when it is only used like an integer.

6. Setting collection1 to Nothing is necessary to clear out the ranges for the current worksheet before going to the next. If there were a .Clear method, that would work too. (But there isn't, at least in Excel 97.)

With that said, here is a slightly re-formatted version of your procedure:

Option Explicit

Sub mycollection()
Dim collection1 As New Collection
Dim MasterCollection As New Collection
Dim q As Byte
Dim myarray As Variant
Dim i As Integer
Dim myrange1 As Range
Dim Index As Variant
Dim MyRange As Range
Dim item As Collection

  On Error Resume Next
  myarray = Array("interstate", "offshore", "intrastate")
  For i = 1 To ThisWorkbook.Sheets.Count
    Set myrange1 = ThisWorkbook.Sheets(i).UsedRange.Find(what:="Usage")
    If Not myrange1 Is Nothing Then
      collection1.Add Sheets(i)
      For Each Index In myarray
        Set MyRange = ThisWorkbook.Sheets(i).UsedRange.Find(what:=Index)
        If Not MyRange Is Nothing Then collection1.Add MyRange
      Next Index
      MasterCollection.Add collection1
    Else
    End If
    Set collection1 = Nothing
  Next i
  
  For Each item In MasterCollection
    For q = 2 To item.Count
      MsgBox item(1).Name & " " & item(q).Address
    Next q
  Next item

End Sub

 

[Luis939]

Thanks a millll Zanthras, your the mannnn thanks for the improvements....as for your questions
well i put q as byte becuase i read that its values dont range as large as integer, i figured maybe it would take up less memory????, but wasnt sure
thanks for the option explicit tip, ill defintely use it from now on
yea ill always usually have those strings on my sheets and there is only one instance of that string if it is in the sheet
sorry for the indent confusions, just was in a rush to get this out as soon as it worked
and most importantly, i still would be curious to see what it would look like when u mentinoed to store those objects as strings, i never thought bout expanding my logic n thinkin....perhaps u could show me code samples....thanks n sorry for delayin u on ur stars

 

[Zathras]

It's all a matter of which way you want to go. (But there still may be the issue of memory leaks.) Saving the object pointers works just fine. If you want to save the names as strings instead, you can change the first .Add statement to

    collection1.Add Sheets(i)

[blue]

.Name

[/color]
and change the second one to

    If Not MyRange Is Nothing Then collection1.Add MyRange

[blue]

.Address

[/color]
and then use your original form of the MsgBox statement:

    MsgBox item(1) & " " & item(q)

or if your prefer, replace the MsgBox line with
[blue]

      With Worksheets(item(1))
        Set MyRange = .Range(item(q))
        MsgBox item(1) & " " & MyRange.Address
      End With

[/color]
thus illustrating that all you need to save in the collection are the names of the sheet and ranges and you can still operate on the ranges as ranges at a later time.

 

[Luis939]

BEAUTIFUL!!! THANKS AGAIN!!!!!!