VBA Store cell references in an array to loop through afterwards in excel

[wmbb]

I want to store cells, matching some criteria, in an array and loop through these cells to show the results.

I already have a code to select the cells and can store the data in a 2 dimensional array.
The stored cells are in different sheets also...
What do I have to store in the array for later use selecting these cells subsequently.
Now I've stored the cell.address.
The stored data for the cells looks like "$F$123" and seems to be a string value.

Does someone know how to store the cell references in the array so I can use them to select these cells ?

 

[SkipVought]

Hi,

Assuming that the data of interest in F123 is on the ActiveSheet...

x = "$F$123"
Debug.Print ActiveSheet.Range(x).Value

The stored cells are in different sheets also...

Well where is the Sheet Name stored? You DEFINITELY need it!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here’s a suggestion. Use Named Ranges rather than A1 references. Your sheet and code would be much easier to understand.

Might be better if you would explain what you’re trying to accomplish by storing cell references. Some of our experienced members might have other ideas that could be of help tp you.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[wmbb]

Thank you for your idea's so far...
What basically happens is that I search all sheets for the filled date-fields in a named range and store them in an array together with the corresponding cell address.
After that I sort the array and want to loop through the date-cells and show them in chronological order.

It's no problem to store the sheetname also like sheet2!"$F$123" or anything else
So in what format do I have to save the string to approach the cells afterwards ?
In this case named ranges is not an option I think...

Code for filling the array with dates and the corresponding cell references..

counter = 0
For Each ws In Worksheets
    If ws.Name = "template" Then GoTo nextsheet
        For Each cell In ws.Range("SerieDateArea")
            If Not IsDate(cell.Value) Then GoTo nextserie
            TargetSeries(counter, 0) = cell.Value
            TargetSeries(counter, 1) = ws.CodeName & "!" & cell.Address
            counter = counter + 1
nextserie:
        Next
    
nextsheet:
Next

 

[SkipVought]

Named Ranges can be global (workbook) or local (sheet)

As long as ALL your Range Names are unique, they will be global and thus inherit the sheet name.

For instance, if StartDate were on Sheet1 and EndDate were on Sheet2, there would be no need to store the sheet name to determine the unique cell for each.

Debug.Print [StartDate].Value
Debug.Print [EndDate].Value

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

You do realize that this defines each date cell on each sheet...

for each ws in worksheets
   for i = 1 to ws.Range("SerieDateArea").count
      debug.print ws.Range("SerieDateArea")(i)
   next
next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[wmbb]

Hi SkipVought,

Yes I realize that all cells within the named range SerieDateArea filled with a date wil be stored in the array, that was mend to be.
I've solved the problem adding another field in the array so I can store the sheet name and cell address separately.

counter = 0

For Each ws In Worksheets
    If ws.Name = "template" Then GoTo nextsheet
        For Each cell In ws.Range("SerieDateArea")
            If Not IsDate(cell.Value) Then GoTo nextserie
            TargetSeries(counter, 0) = cell.Value
[COLOR=#EF2929]            TargetSeries(counter, 1) = cell.Address
            TargetSeries(counter, 2) = ws.Name
[/color]            counter = counter + 1
nextserie:
        Next
    
nextsheet:
Next

After filling the array I can loop through the date-cells using

For i = 0 To UBound(TargetSeries, 1)
        Set ws = Sheets(TargetSeries(i, 2))
        Set cell = ws.Range(TargetSeries(i, 1))
        ws.Activate
        ActiveWindow.ScrollRow = cell.Row
        cell.Activate
.......

But thanks for pointing me in the right direction !

 

[SkipVought]

How about this without GoTo. Much easier to understand

counter = 0

For Each ws In Worksheets
    Select Case ws.Name 
        Case "template"
            For Each cell In ws.Range("SerieDateArea")
                Select Case IsDate(cell.Value) 
                    Case True
                        TargetSeries(counter, 0) = cell.Value
                        TargetSeries(counter, 1) = cell.Address
                        TargetSeries(counter, 2) = ws.Name
                        counter = counter + 1
                End Select
            Next
    End Select
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[wmbb]

Hi Skip,

Sorry for the late reply...
Off course I can use the case option but in your suggestion you read the data from the sheet named "template" and that is just the sheet I want to exclude

 

[SkipVought]

counter = 0

For Each ws In Worksheets
    Select Case ws.Name 
        Case "template"
        [b]Case Else[/b]
            For Each cell In ws.Range("SerieDateArea")
                Select Case IsDate(cell.Value) 
                    Case True
                        TargetSeries(counter, 0) = cell.Value
                        TargetSeries(counter, 1) = cell.Address
                        TargetSeries(counter, 2) = ws.Name
                        counter = counter + 1
                End Select
            Next
    End Select
Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]