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!

 

[Bowers74]

MsgBox MyArray(1,1)

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Luis939]

thanks, well actually i would like to have away without explicitely writing in the code the indices themselves, i want it to run thru each index and then spit out the index id

 

[Bowers74]

You could create a collection, collections can be sorted, and I believe they can be searched as well.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Luis939]

yea i tried working with them but its really confusing, basically the reason i was using a 2-dimensional array was becuase 1 dimension represented certain worksheets in my workbook and the other represented ranges,
but after trying, i wasnt able to use collections for something dealing with 2 dimensions?? any thoughts??

 

[Bowers74]

Have you tried using Named Ranges?

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Luis939]

well its real complicated, i need to write a macro that checks to see if certain worksheets have certain strings, so when i first determine one worksheet is to be used, i wanna store that sheet, then i go ahead and test that worksheet to see if it has certain strings, if it does have a certain string i want to be able to store the range where that string is and then when i determine what strings that worksheet has or has not i move on to the next worksheet and test to see if that worksheet is to be used, and i determine that if it has one additional string
so basically later in the macro i want to be able to recall those worksheets (so i dont have to retest each worksheet) and then use those ranges for each worksheet..im not sure if i should use collections or arrays or what??

 

[TonyJollans]

Hi 4335,

You can't. Item{/I] is not an Object and doesn't have any Properties.

AFAIK your loop will run through the items in order so you could keep a count based on lbound (for a one-dimensional array at least) - but then you might as well use it as an index and have it drive the loop. If you have a multi-dimensional array and you don't know how many dimensions it has there is no easy way to find out; you could try something on these lines ..

n = 1
Do
  on error resume next
  if lbound(myarray, n) > 0 then n = n+ 1 else n = n + 1
  if err.number = 9 then exit do
loop
msgbox "my array is " & n & "-dimensional"

Enjoy,
Tony

 

[Luis939]

well it would have been nice to use something that resembles a 2-dimensional collection object, i remember reading something about a "dictionary" object, but frankly i think it would be easier (and less code) to just take the strings that i found, put them in cells in column IV or something and then have my program just scan those cells and proceed from there, instead of writing up some code (that might not even work) to try to store alll that data, which will make the program run unnecessarily long. It might seem elementary, but it works.

 

[Zathras]

I can't quite figure out what you are really doing, but just for the hey of it, try playing around with this. It demonstrates how you can have a collection of collections.

Set up a new worksheet with 3 sheets.
Put something in A1:A3 of each sheet that you will recognize
Make the array size as large as the largest case you expect to encounter.
Run the test macro.
[blue]

Option Explicit
Public MasterCollection As Collection
Public MasterSheetIndex(400) As String

Sub test()
Dim sht As Worksheet
Dim i As Integer
  Set MasterCollection = New Collection
  
  For Each sht In Worksheets
    GetData sht
  Next sht
  
  For i = 1 To MasterCollection.Count
    ShowData i
  Next i

  Set MasterCollection = Nothing
End Sub

Private Sub GetData(ASheet As Worksheet)

[green]

' Get data from one sheet:
'     save data in a collection
'     add collection to MasterCollection
'     store sheet name in MasterSheetIndex

[/color]

Dim oCollection As New Collection

  With ASheet
    oCollection.Add (.Range("A1").Value)
    oCollection.Add (.Range("A2").Value)
    oCollection.Add (.Range("A3").Value)
    MasterCollection.Add oCollection, .Name
    MasterSheetIndex(MasterCollection.Count) = .Name
  End With
  
End Sub

Private Sub ShowData(Index As Integer)

[green]

' Show data from one collection

[/color]

Dim oCollection As Collection
Dim i As Integer

  Set oCollection = MasterCollection(Index)
  For i = 1 To oCollection.Count
    MsgBox MasterSheetIndex(Index) & "   " & oCollection(i)
  Next i
  
End Sub

[/color]

 

[Zathras]

As an afterthought: Before setting the master collection to nothing, you should probably loop thru the collection one last time and set each collection in the collection to nothing or you may have memory leakage.

 

[Luis939]

wow thats incredible i really appreciate your contribution Zanthras, as always, i dont have time to play around with your code but i promise i will on Monday, luckily my program is not that complex, i simply wanted to test each worksheet to see if it contained certain strings and then test to see if those strings are valid n if so record the sheet, then after im done testing each sheet, i go back to only those sheets that have valid strings, and then use each string for something else....so i just wanted to know the different ways i could store data and one of those ways was thru something probably 2 dimensional, where i stored both the worksheet and a list of all ranges that contain valid strings so that i dont have to go back and revisit everyyyy worksheettt and everyyyyy string in those sheets,
so i just wanted everyone's opinion as to what would be the best approach to storing that data efficiently
so thanks alot and we'll see what happens

 

[Luis939]

OK Zanthras, maybe becuase im rushing here but you might be solving my problem, but basically (and again im only assuming collections are the best way to approach this) but yes i would like to store certain worksheets that meet a certain condition, and if they do meet that condition, they i simply look for ranges such as
for i to thisworkbook.sheets.count
set myrange = thisworkbook.sheet(i).usedrange.find(what:="blahblahblah&quot
if not myrange is nothing
'add this sheet to my collection since it found something

but perform this step a few other times for other ranges within that same sheet

and then keep doing that successfully until I have tested each worksheet....then I would simply like to go back and work with only those sheets that I have stored one by one, first going to the first sheet and then working with each of the ranges i have stored for only that first sheet recorded, and then moving on to the next sheet recorded for its ranges recorded until i have finished with the last sheet i recorded, ill keep studying your code, but hopefully you can clear this up for me, thanks alot!!!

oh and p.s. i wasnt quite sure what you meant about memory leakage and setting the collection to nothing, what does that do and how would i do that thanks!

 

[Zathras]

This should be a little better fit for what you are doing:
[blue]

Option Explicit
Public MasterCollection As Collection

Sub test()
Dim sht As Worksheet
Dim i As Integer
Dim col As Collection

  Set MasterCollection = New Collection

[green]

  ' Loop thru all sheets to find stuff to be processed

[/color]

  For Each sht In Worksheets
    GetData sht
  Next sht

[green]

  ' Loop thru all sheets to process stuff

[/color]

  For Each sht In Worksheets
    ProcessData sht
  Next sht

[green]

  ' Free up memory assigned to collections

[/color]

  For Each col In MasterCollection
    Set col = Nothing
  Next col
  Set MasterCollection = Nothing
End Sub

Private Sub GetData(ASheet As Worksheet)

[green]

' Get data from one sheet:
'     save range names in a collection
'     add collection to MasterCollection

[/color]

Dim oCollection As New Collection
Dim MyRange As Range
Dim FirstAddress As String
Dim arrSearchWords As Variant
Dim i As Integer

  arrSearchWords = Array("blahblahblah", "bleepbleepbleep", _
                       "foofoofoo", "etc.etc.etc")
  With ASheet
    For i = LBound(arrSearchWords) To UBound(arrSearchWords)
      Set MyRange = .UsedRange.Find(what:=arrSearchWords(i))
      If Not MyRange Is Nothing Then
        FirstAddress = MyRange.Address
        Do

[green]

          'add this sheet to my collection since it found something

[/color]

          oCollection.Add MyRange.Address
          Set MyRange = .UsedRange.FindNext(MyRange)
        Loop While Not MyRange Is Nothing _
                   And MyRange.Address <> FirstAddress
      End If
    Next i
    If oCollection.Count > 0 Then
      MasterCollection.Add oCollection, .Name
    Else
      Set oCollection = Nothing
    End If
  End With
End Sub

Private Sub ProcessData(ASheet As Worksheet)

[green]

' Process data in one sheet:
'    Collection contains range names to process

[/color]

Dim oCollection As Collection
Dim i As Integer

  With ASheet
    Set oCollection = MasterCollection(.Name)
    For i = 1 To oCollection.Count

[green]

      ' Demonstrate accessing data from the collection

[/color]

      With .Range(oCollection(i))
        MsgBox ASheet.Name & "!" & .Address & ": " & .Value
      End With
    Next i
  End With
End Sub

[/color]

 

[Luis939]

The code defintely seems to give me what i want, but i think for 2nd for loop

' Loop thru all sheets to process stuff
For Each sht In Worksheets
ProcessData sht
Next sht

that should read for each sht in mastercollection, should it not?? becuase i dont want to scan each worksheet again,
but i try it and it gives me an error, perhaps i didnt declare it for that sub? thanks!!

 

[Zathras]

You wouldn't be scanning each worksheet again, merely using it (actually it's name) as a way to access the collection of range names pertaining to that sheet.

But I left out a step. In case there is a sheet on which none of the searched for items are found, there will be no collection created. Insert this line:
[blue]

    On Error Resume Next

[/color]
in front of this line:
[blue]

    Set oCollection = MasterCollection(.Name)

[/color]
in the [blue]

 ProcessData

[/color] sub.

If you loop thru the MasterCollection directly, you won't know which sheet to process. (Unless you make it more complicated by putting the sheet name as Item[0] of the collection and begin putting the range addresses in Item[1].) It's a quirk of collections that you can access items by key but you can't access the key itself. (At least I haven't figured out a way to do that yet.)

 

[Luis939]

Yea the thought of placing the sheet name as Item[0] did cross my mind and i tried tinkering around with it, but it just frustrated me.

 

[Luis939]

Although I think I read that you can access the keys with a Dictionary object? Hmm...

 

[Luis939]

Im a little confused as to why you added .name in
MasterCollection.Add oCollection, .Name

and what this does
Set oCollection = MasterCollection(.Name)

and why did you write
With .Range(oCollection(i))
how does it know that you were talking about a range??
Thanks!

 

[Luis939]

I know im getting pretty annoying now, but let say we already know where the sheet.name will be stored within the first collection or even master collection

cant i just have a variable like collectionitem assigned that location or index so that i can use it

set myrange = thisworkbook.sheets(collectionitem)......

just wanted to save the time of referencing all worksheets again, so i thought the collection would save me that