×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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 ?

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

Hi,

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

CODE

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

Quote:

The stored cells are in different sheets also...
Well where is the Sheet Name stored? You DEFINITELY need it!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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..

CODE --> en

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 

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

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.

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

CODE

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


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

CODE -->

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) = cell.Address
            TargetSeries(counter, 2) = ws.Name
            counter = counter + 1
nextserie:
        Next
    
nextsheet:
Next 

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

CODE -->

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 !

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

How about this without GoTo. Much easier to understand

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

CODE

counter = 0

For Each ws In Worksheets
    Select Case ws.Name 
        Case "template"
        Case Else
            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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close