Solved

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

Posted on 2011-09-20
12
297 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
  • 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 500 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

zlib is a free compression library (a DLL) on which the popular gzip utility is built.  In this article, we'll see how to use the zlib functions to compress and decompress data in memory; that is, without needing to use a temporary file.  We'll be c…
With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now