Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Copying values of named ranges from one worbook to another using an array

Posted on 2011-09-20
12
Medium Priority
?
312 Views
Last Modified: 2012-08-13
 
 
Hi Experts
 
I am trying to  write some code where I am copying the values of some named ranges in a workbook on a certain date to the same named ranges in the same workbook of a different date.  The file names of the 2 workbooks are the same except they file date is incorporated into the name so this distinguishes the 2:
 
File 1:  ABCD110916 (file for 16th September 2011)  
File 2:  ABCD110915 (file for 15th September 2011)
 
I am running the code all from a separate control workbook which opens the 2 workbooks and retrieves the data and then closes and saves the changes.  In the control workbook I have created a range in a sheet which lists all the named ranges and the worksheets they are located in in the workbooks which I am copying from /to.  This range gets copied into an array “myarray” with the first element of the array storing the Worksheet name, the 2nd element storing the name of the range.  Then I am  trying to open ‘File 1’ and retrieve the value of the named range specified by the array and store that in the 3rd element – Range_Value
 
Array Structure:
 
 
1      Worksheet      Named_Range      Range_Value
2      Worksheet      Named_Range      Range_Value
3      Worksheet      Named_Range      Range_Value
 
 
When I run the code I get a subscript out of range error when it tries to assign the value of the named range to the 3rd element of the subscropt (Range_Value)
 
Can anyone help please.
 
Many Thanks
 
Asim
 
 
Sub Find_Results()
 
Application.ScreenUpdating = False
 
Dim CurCell As Object
Dim fso
 
Dim filedate As String
Dim filedate2 As String
 
Dim results_template As String
Dim results_file As String
Dim book_folder As String
Dim results_path As String
Dim book_name As String
Dim date_type As String
Dim book_ccy As String
 
Dim MyArray() As Variant
 
MyArray = Range("Name_Range").Value
 
 
filedate = Format(Range("Bus_Date").Value, "yymmdd")
filedate = Replace(filedate, "/", "")
filedate2 = Format(Range("Bus_Date").Value, "dd/mm/yyyy")
 
results_template = Range("results_template").Value
 
 
For Each CurCell In Range("Folders")
 
If CurCell.Offset(0, 4).Value Then
 
                         book_folder = CurCell.Value
                         results_path = book_folder & "Results\" & CurCell.Offset(rowOffset:=0, columnOffset:=1).Value & "SR" & filedate & ".xls"
                         results_file = CurCell.Offset(rowOffset:=0, columnOffset:=1).Value & "SR" & filedate & ".xls"
 
 
                         book_name = CurCell.Offset(rowOffset:=0, columnOffset:=1).Value
                         date_type = CurCell.Offset(rowOffset:=0, columnOffset:=3).Value
                         book_ccy = CurCell.Offset(rowOffset:=0, columnOffset:=2).Value & "FndCurve"
 
 
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(results_template) Then
 
 
                                         CurCell.Offset(rowOffset:=0, columnOffset:=5).Value = "Template Results Summary Not Found"
                                         CurCell.Offset(rowOffset:=0, columnOffset:=5).Interior.ColorIndex = 3
 
Else
 
 
fso.CopyFile results_template, results_path
 
Application.Workbooks.Open results_path, False, False
 
 
Workbooks(results_file).Worksheets("UserConfiguration").Range("BookName").Value = book_name
Workbooks(results_file).Worksheets("UserConfiguration").Range("Reporting_Currency").Value = book_ccy
 
                                  'Looping structure to look at array.
 
                                         For i = 1 To UBound(MyArray)
                                                 Debug.Print MyArray(i, 1) & " " & MyArray(i, 2)
 
                                                 MyArray(i, 3) = Workbooks(file).Worksheets(MyArray(i, 2)).Range(MyArray(i, 1)).Value
 
                                                 Debug.Print MyArray(i, 1) & " " & MyArray(i, 2) & " " & "******" & MyArray(i, 3)
 
                                         
 
                                          Next
 
 
                                         Workbooks(results_file).Close savechanges:=True
 
 
Application.DisplayAlerts = False
 
 
End If
 
 
End If
 
Next
 
 
End Sub
 

0
Comment
Question by:simsima_7876
[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
  • 6
12 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36567311
I think your problem if that when you say:
MyArray(i, 3) = Workbooks(file).Worksheets(MyArray(i, 2)).Range(MyArray(i, 1)).Value

Open in new window

the variable 'file' is not defined, and therefore defaults to empty, and the subscript out of range error is on the Workbooks collection.

You can avoid this sort of problem by using Option Explicit at the top of the module to force variable declaration to be explicit.  I'm not sure what  variable you meant to type there, but I expect you know.  If you need more help then let us know.
0
 

Author Comment

by:simsima_7876
ID: 36567726
Hi Andrew  

That's done the trick.  Can I be so bold as to ask you one more question?  I've added the following line of code to output the contents of the array to the control workbook:

  Range("Range_Value").Offset(rowOffset:=i, columnOffset:=0).Value = MyArray(i, 3)

I've added this within the loop- after the line:

             MyArray(i, 3) = Workbooks(file).Worksheets(MyArray(i, 2)).Range(MyArray(i, 1)).Value

This gives me the error:

Run-time error '1004'
Method 'Range' of object '_Global' failed

Any ideas?  I will assign you the points for the original question regardless of whether you can help me with this.


0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 36567789
Do you need to qualify it with the name of the workbook? - as you do in the line
Workbooks(results_file).Worksheets("UserConfiguration").Range("BookName").Value = book_name

Open in new window

 If you just say Range I think it will only look for it in the active workbook, and I'm not sure which workbook is active as I can't easily run your code.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:simsima_7876
ID: 36567876
That gives me a subscript out of range error on that line.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36567933
OK give me a minute - I'll try and run it myself
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36567945
Is there a range named Range_Value in the workbook?  It would help if you could post the workbooks you're using as it's not easy to guess what going on otherwise
0
 

Author Comment

by:simsima_7876
ID: 36568023
0
 

Author Comment

by:simsima_7876
ID: 36568033
Yes there is a Range_Value range in the workbook.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36568159
Try putting in ThisWorkbook.Activate before the start of your for loop.  I think the problem is the range you want is in the base workbook, and when you open the results workbook, it becomes active.   I still can't run your code as it depends on so many file locations, but I can't see the harm in switching the bas workbook back to being active:

'Dim myarray As Variant
      'myarray = Range("Names_Sheets").Value

      'Looping structure to look at array.
  
  ThisWorkbook.Activate
  
  For i = 1 To UBound(MyArray)
       
      
    MyArray(i, 3) = Workbooks(results_file).Worksheets(MyArray(i, 2)).Range(MyArray(i, 1)).Value
      
    Debug.Print i & " " & "***"; MyArray(i, 2) & " " & "***" & MyArray(i, 1) & " " & "***" & MyArray(i, 3)
      
      Range("Range_Value").Offset(rowOffset:=i, columnOffset:=0).Value = MyArray(i, 3)
      

Open in new window

0
 

Author Comment

by:simsima_7876
ID: 36568293
Thanks Andrew.  This works.  I will assign you some extra points seeing as you've fixed 2 problems.

0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36568298
No problem
0
 

Author Closing Comment

by:simsima_7876
ID: 36568332
Very helpful expert.  Solved my problem in super quick time.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows a few slightly more advanced techniques for Windows 7 gadget programming, including how to save and restore user settings for your gadget and how to populate the "details" panel that is displayed in the Windows 7 gadget gallery.  …
In this article, I will show how to use the Ribbon IDs Tool Window to assign the built-in Office icons to a ribbon button.  This tool will help us to find the OfficeImageId that corresponds to our desired built-in Office icon. The tool is part of…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

610 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