Solved

Working with an Array stored in the Excel Defined Names Collection

Posted on 2011-03-15
13
231 Views
Last Modified: 2012-05-11
My code creates a 2-D array that I store in the workbook names collection like this:  

Public Sub Save_Default_CV_Report()
Dim arSaveDefault()

Call GetCVRecordset("PDP")     '   RETURNS rsPDP
rsPDP.MoveFirst
rsPDP.Filter = "REPORT = 'Y'"
n = 0

ReDim arSaveDefault(rsPDP.RecordCount, 1)
Do Until rsPDP.EOF
    arSaveDefault(n, 0) = Trim(rsPDP("PROJ_NAME")) & "|" & Trim(rsPDP("MILE_NAME"))
    arSaveDefault(n, 1) = Trim(rsPDP("REPORT"))
    n = n + 1
    rsPDP.MoveNext
Loop
    
ActiveWorkbook.Names.Add Name:="arDefaultCVReport", RefersTo:=arSaveDefault

    
End Sub

Open in new window


I don't know how to  loop thru the array elements to find a value. I've tried using indices like this [myArray](0, 1) using the brackets for the Evaluate Method - that doesn't work. I've also tried assigning the array to a new array variable like this myNewArray = ActiveWorkbook.Evaluate("myArray"), but that doesn't work either. I can't figure out how to address the array and it's elements when I retrieve it from the Names collection. Here's the code I have tried. Can you help?

 
Public Sub PostDefaults()
Dim Projects As Range, c As Range, n As Long, concat As String, arDefault()

'   IDENTIFY DEFAULT PROJECTS WITH A "Y" IN THE REPORT COLUMN
Set Projects = ActiveSheet.Range(Cells(2, 1), Cells(GetLastRow(ActiveSheet, 1), 1))
arDefault = Evaluate("arDefaultCRMReport").RefersTo

For Each c In Projects
    concat = c.Offset(0, 1) & "|" & c.Offset(0, 2)
    
    For n = 0 To UBound([arSaveDefault], 2)
        If arDefault(n, 0) = concat Then
            c.Offset(0, 7).value = arDefault(n, 1)
            Exit For
        End If
    Next
Next

End Sub

Open in new window

0
Comment
Question by:dsimcox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 167 total points
ID: 35139808
change
arDefault = Evaluate("arDefaultCRMReport").RefersTo

to

arDefault = Evaluate(Names("arDefaultCRMReport").RefersTo)

Thomas
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 333 total points
ID: 35139860
You can use:
arDefault = Evaluate("arDefaultCRMReport")

Open in new window

or:
arDefault = [arDefaultCRMReport]

Open in new window

then:
    For n = 0 To UBound(arDefault, 2) 

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139869
Sorry, Thomas - should have refreshed.
Rory
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:nutsch
ID: 35139902
none taken.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139918
Confused, would we? :)
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35139963
I was answering your subtext.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139980
And I, yours.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35140002
un partout, la balle au centre.

T
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35140036
I am often accused of being touché with madness.

Have we scared the Asker away yet, do you think? ;)
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35140196
working on it.
0
 

Author Comment

by:dsimcox
ID: 35140850
OK guys, back to work!

Both of your solutions worked - thanks for the prompt replies -  but there is a problem.

There are 38 records in the recordset rsPDP (I've verified by counting them on the worksheet)
In the following code, the UBound of arSaveDefault is 38. So far, so good.

'   THIS CODE SAVES AN ARRAY OF THE ITEMS IN THE DEFAULT CRM PDP REPORT WHERE REPORT = 'Y'
Public Sub Save_Default_CRM_Report()
Dim arSaveDefault(), n As Long

Call GetCRMRecordset("PDP")     '   RETURNS rsPDP
rsPDP.MoveFirst
rsPDP.Filter = "REPORT = 'Y'"
n = 0

ReDim arSaveDefault(rsPDP.RecordCount, 1)
Do Until rsPDP.EOF
    arSaveDefault(n, 0) = Trim(rsPDP("PROJ_NAME")) & "|" & Trim(rsPDP("MILE_NAME"))
    arSaveDefault(n, 1) = Trim(rsPDP("REPORT"))
    n = n + 1
    rsPDP.MoveNext
Loop
    
ActiveWorkbook.Names.Add Name:="arDefaultCRMReport", RefersTo:=arSaveDefault

    
End Sub

Open in new window



But when I retrieve the array from Names and assign it to the variant array arDefault in line 6, there are 39 records in it. The last record arDefault(39, 1) is "Error 2042". That's "N/A". Something seems to be happening to that array when it is saved in the Names collection and then retrieved using this code that you helped me with. It's not the same as it was going in. Any ideas?

 
Public Sub PostDefaults()
Dim Projects As Range, c As Range, n As Long, concat As String, arDefault()

'   IDENTIFY DEFAULT PROJECTS WITH A "Y" IN THE REPORT COLUMN
Set Projects = ActiveSheet.Range(Cells(2, 1), Cells(GetLastRow(ActiveSheet, 1), 1))
arDefault = Evaluate("arDefaultCRMReport")

For Each c In Projects
    concat = c.Offset(0, 1) & "|" & c.Offset(0, 2)
    For n = 1 To UBound(arDefault, 1)
        If arDefault(n, 1) = concat Then
            c.Offset(0, 7).value = arDefault(n, 2)
            Exit For
        End If
    Next
Next

End Sub

Open in new window

0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 333 total points
ID: 35141021
Your array is zero based but you are sizing it to the recordcount. You should size it to recordcount-1.
0
 

Author Comment

by:dsimcox
ID: 35147551
Of course . . . Thanks for pointing this out. Good work!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question