Help with linked workbooks, VBA, and arrays

[Masterpsyker]

Hello again Mr. Excel forums,

I have run into a problem with my current project as my VBA knowledge isn't exactly "great" (I was asked to start this scripting project 3 days ago by my boss with NO prior knowledge of what VBA even was) but my comp-sci is on par with an "average" programmer.

I have a very long haul ahead of me from a coding perspective with the Kansas City Southern Railroad annual R-1 report. My boss has asked me make a "master" spreadsheet that is to be UNALTERED by ANYONE.

The workbook for the R-1 is supposed to move all the "End Year" entries to the "Beginning Year" entry columns inside the R-1 report and then update the current year adjustments and current end year values to the Workbook.

There are 42 Workbooks which will all be linked (either direct or indirect I don't know yet) to their corresponding worksheet inside the master macro file. The problem with this is that the linking must be done as a "per-cell" issue because the structure of the workbooks does not match the structure of the R-1 report.

I've tried to talk my boss into having each accounting department into revamping their workbook structure to make this very easy, but they are all very adamant in having their workbooks stay the way they are... the only thing they will do is follow a naming convention for their final product so that it makes it easy to assign the file path with a script each year.

Given that information, here are the things I would currently like to know how to do. I don't want people to do my job for me, but I need to know how these methods work or can be done so that I can complete this project by Monday.

1) How do I use a loop to assign a list of ranges into an array? The range names follow the convention RangeX where X is an interger >=1. This is my problem below:

Dim Range1 as Range '5 more of these for sheet 200
Range1 = ThisWorkbook.Worksheets("200").Range("F11:F24") '5 more of these too
Option Base 1
Dim workArray(6)
For X = 1 To 6
workArray(X) = RangeX
Next X

I do not know how to get the "RangeX" part to work as an incrementation of the loop. The ranges are manually named Range1, Range2, RangeN, and I do not know how to tack the loop-variable X onto the end of the word "Range" and have VBA interpret that combo of "Range" + "X" as the object RangeX

2) Can you use the following code:

dim path as String
path = ThisWorkbook.Worksheets("Declarations").[A13].Formula
For each cell in Range1 ' or RangeX if I can stick this in another loop
cell.Formula = "='" & path & "SCH " & ThisWorksheet.Name & "\[" & ThisWorksheet.Name & "_FINAL.xls]" & ThisWorksheet.Name & "'!" & cell.Address
next cell

That was supposed to assign the formula of every cell in the range as a link to another workbook in a path specified in cell A13 on the "Declarations" worksheet in the macro's workbook. The complete file path would read something like "G:\KCSRACCT\AAR STB Reporting\R-1 - 'YEAR'\SCH XXX\XXX_FINAL.XLS" where XXX is the active worksheet's name, and year is obviously the year.

3) When you print from a macro is there any way to ensure that pre-set print areas are followed? On that subject, what's the command to print from a macro anyway? "Object.Print?"

4) Could I use "ActiveCell.Offset(C,R).Formula = ActiveCell.Formula" to assign the VALUE of the active cell to the offset specified?

5) I've tried to make a script to automate a "find and insert" type of code which grabs information based on a cell reference and then grabs the index for that cell reference. It looks like this:

Sub SAP_TO_CX_FINAL()
' Create a new workbook
Set wbNew = Workbooks.Add
wbNew = ActiveWorkbook.Name
' Variable Declarations for later
Dim R As Integer
Dim L As String
Dim string1 As String
Dim Data As String
' Dummy declarations to fill memory
R = 0
L = "string"
string1 = "string1"
Data = "Data"
K = 0
Ke = 0
Z = 0
' Prompt User to select the SAP sheet from THIS YEAR and open it
ChDir "G:\KCSRACCT\AAR STB Reporting"
msgBox ("Select the Opperating Expense Sheet from THIS YEAR and open it.")
Application.Dialogs(xlDialogOpen).Show
' Assigns the wbSAP reference to the SAP workbook
wbSAP = ActiveWorkbook.Name
' Routine to copy ActiveWorksheet from the SAP workbook to FinalVariance
    Workbooks(wbSAP).Activate
    Sheets("R-1 Assign").Select
    Sheets("R-1 Assign").Copy Before:=Workbooks(wbNew).Sheets(1)
 
' Input Box to get CX_Sheet workbook path and filename into Path and Filename strings
ChDir "G:\KCSRACCT\AAR STB Reporting"
msgBox ("Select the Variance Report from last year and open it.")
Application.Dialogs(xlDialogOpen).Show
' Assigns the wbCX reference to the CX workbook
wbCX = ActiveWorkbook.Name
' Routine to copy ActiveWorksheet from the CX_Sheet workbook to FinalVariance AND CLOSE THE CX_SHEET WHEN DONE
    Workbooks(wbCX).Activate
    Sheets(1).Select
    Sheets(1).Copy Before:=Workbooks(wbNew).Sheets(1)
' Declare References to worksheets for later
Set wsVariance = Workbooks(wbNew).Worksheets(1)
Set wsR1 = Workbooks(wbNew).Worksheets(2)
Set wsStorage = Workbooks(wbNew).Worksheets(3)
' At this point in time, we now have a workbook that we created at the begining of the execution lines and two open workbooks from which we browsed to
' Inside our new workbook are 5 sheets: The 3 that are created by default, and the two sheets which have been copied.
' The SAP Workbook (with the R-1 assignments) worksheet will be referencing the active SAP workbook so it cannot be closed until the macro is done.
' The copied worksheet will be handled "values only" via the string copy to ActiveCell.Offset(X, X).FormulaR1C1 so that the final result in the
' FinalVariance CX_Sheet is values which are independent of any other workbook at close.
' Before we can copy the R-1 Assignments into the Variance Report, we need to run the sequence which clears current year into previous year.
' Clears "Prior Year" data
For C = 7 To 22 Step 5
    For R = 1 To 10
        wsVariance.Activate
        If R = 1 Then
            K = 16
            Ke = 97
        End If
        If R = 2 Then
            K = 101
            Ke = 118
        End If
        If R = 3 Then
            K = 121
            Ke = 138
        End If
        If R = 4 Then
            K = 141
            Ke = 163
        End If
        If R = 5 Then
            K = 168
            Ke = 185
        End If
        If R = 6 Then
            K = 188
            Ke = 202
        End If
        If R = 7 Then
            K = 205
            Ke = 209
        End If
        If R = 8 Then
            K = 212
            Ke = 221
        End If
        If R = 9 Then
            K = 224
            Ke = 232
        End If
        If R = 10 Then
            K = 236
            Ke = 253
        End If
        Range(Cells(K, C), Cells(Ke, C)).Select
        Selection.ClearContents
    Next R
Next C
' Copies CurrentYear to Storage Worksheet preserving cell format and data (and comments)
' C and K/Ke function here as the Colum/Row reference for Range(Cells(R, C), Cells(R, C)).Select
For C = 6 To 21 Step 5
    For R = 1 To 10
        wsVariance.Activate
        If R = 1 Then
            K = 16
            Ke = 97
        End If
        If R = 2 Then
            K = 101
            Ke = 118
        End If
        If R = 3 Then
            K = 121
            Ke = 138
        End If
        If R = 4 Then
            K = 141
            Ke = 163
        End If
        If R = 5 Then
            K = 168
            Ke = 185
        End If
        If R = 6 Then
            K = 188
            Ke = 202
        End If
        If R = 7 Then
            K = 205
            Ke = 209
        End If
        If R = 8 Then
            K = 212
            Ke = 221
        End If
        If R = 9 Then
            K = 224
            Ke = 232
        End If
        If R = 10 Then
            K = 236
            Ke = 253
        End If
        Range(Cells(K, C), Cells(Ke, C)).Select
        Selection.Cut
        Z = C + 1
        Range(Cells(K, Z)).Select
        ActiveSheet.Paste
    Next R
Next C
 
' R (for right-hand) is the number that is read off the R-1 Assign Coulmn "A" string and stored to search for the Excel row
' L (for letter) is the character returned by the Right function and used to position the Excel Column
' Data is the variable which will hold the numbers from the SAP wkbk until they are transfered to the CX Sheet
' Activate the SAP Operational Expense SHEET @ Cell A3
wsR1.Range("A3").Activate
' Activate loop for data input
counter = 0
Do
    ' Establish Data from adjacent cell, read string in A as two parts (Number + Letter)
    Data = ActiveCell.Offset(0, 1).Text
    R = Val(ActiveCell.Formula)
    L = Right(ActiveCell.Formula, 1)
    ' Activate Cx Sheet for searching and input
    wsVariance.Range("B17").Activate
    'Loop to find correct row according to number R
    Do
        If ActiveCell.Formula = R Then
            Exit Do
        Else
            ActiveCell.Offset(1, 0).Activate
            End If
    Loop
    If L = "B" Then
        ActiveCell.Offset(0, 4).FormulaR1C1 = Data
    ElseIf L = "C" Then
        ActiveCell.Offset(0, 9).FormulaR1C1 = Data
    ElseIf L = "D" Then
        ActiveCell.Offset(0, 14).FormulaR1C1 = Data
    ElseIf L = "E" Then
        ActiveCell.Offset(0, 19).FormulaR1C1 = Data
    End If
    counter = counter + 1
    wsR1.Range("A3").Activate
    ActiveCell.Offset(counter, 0).Activate
    string1 = ActiveCell.Offset(1, 0).Formula
    If string1 = "" Then
        Exit Do
    End If
Loop
Windows(wbSAP).Activate
ActiveWorkbook.Close SaveChanges:=False
End Sub

It malfunctions and assigns numbers in the wrong places, but I can't figure out why. A copy of this workbook is available upon request. I need to fix this script by Thursday of next week. Honestly, I don't know what's wrong.

-Masterpsyker

 

[Masterpsyker]

Posting to bump in hopes that someone will help.

 

[Kenneth Hobson]

Not sure just what you need. While I will tell you how to put a list of ranges into an array, I am not sure that is the best route. Union() is often used for that sort of thing. Or, one can also just send the parts like sheet names or range names to a sub multiple times. Just depends on what you are doing.

For (1):

'Option Base 1 'Is this needed?
Sub DoingSomethingHere()
  Dim workArray(1 to 6) as Range
  Dim X as Integer, Y as Integer

  Y = 1
  For X = 1 To 6
    Y = Y + 1
    Set workArray(X) = ThisWorkbook.Worksheets(CStr(100*Y)).Range("F11:F24") 'Sheets: 200-700 
  Next X

  'Show address of ranges in Immediate Window:
  For X = 1 to
    Debug.Print "workArray(" & X & ")", workArray(X).Address
  Next X
End Sub

For (2):

Sub Routine2()
  Dim path as String, cell as Range, Range1 as Range
  Set Range1 = ThisWorkbook.Worksheets("200").Range("F11:F24")
  'Are you sure that Formulas is the path's name?  Typically, it would be Value.
  path = ThisWorkbook.Worksheets("Declarations").[A13].Value
  For each cell in Range1 ' or RangeX if I can stick this in another loop
    cell.Formula = "='" & path & "SCH " & ThisWorksheet.Name & "\[" & ThisWorksheet.Name & "_FINAL.xls]" & ThisWorksheet.Name & "'!" & cell.Address
    debug.print cell.address, cell.Formula, cell.Value
  next cell
End Sub

See if this makes sense and then we will try the other questions.

 

[Masterpsyker]

Mr. Hobson,

Yes, you were right, it is not Range.Formula, but rather Range.Value. I have changed the code accordingly.

I now have this (pardon the giant list of Dim Range, Set Range... I didn't want to waste typing time I already put in from attempting to go through an array holding ranges):

Sub SCH_200_Transpose()
'For the path format to work you need to grab the path in the Declarations worksheet
'which will point at: G:\KCSRACCT\AAR STB REPORTING\R-1 - 2008\
'and then add the \SCH XXX\XXX_FINAL.XLS attachment
'You will need the worksheet name for the XXX and that's it
'Declare Path from Declarations-A13
Dim path As String
path = ThisWorkbook.Worksheets("Declarations").[A13].Value
Dim Range1 As Range
Dim Range2 As Range
Dim Range3 As Range
Dim Range4 As Range
Dim Range5 As Range
Dim Range6 As Range
Set Range1 = Range("F11:F24")
Set Range2 = Range("F27:F35")
Set Range3 = Range("F38:F41")
Set Range4 = Range("F59:F68")
Set Range5 = Range("F71:F81")
Set Range6 = Range("F84:F93")
Dim BigRange As Range
Set BigRange = Union(Range1, Range2, Range3, Range4, Range5, Range6)
'Initialize CELL as a range for reference during loop
Dim cell As Range
path = ThisWorkbook.Worksheets("Declarations").[A13].Value
For Each cell In BigRange
    cell.Copy
    cell.Offset(0, 1).PasteSpecial (xlPasteValues)
    cell.Formula = "='" & path & "SCH " & ActiveSheet.Name & "\[" & ActiveSheet.Name & "_FINAL.xls]" & ActiveSheet.Name & "'!" & cell.Address
    Debug.Print cell.Address, cell.Formula, cell.Value
Next cell
End Sub

Now I have a new problem: When the loop iterates and the formula for the cell updates the "open file" dialogue box pops open. Why is this? The folder referenced by the formula of the cells exists and the only worksheet in that file has the exact name as defined by the path. Any ideas?

 

[Masterpsyker]

I would have editted my previous post, but after searching for the edit button I was unable to find it.

Issues 1, 2, and 4 are now resolved.

Only issued 3 and 5 remain unsolved.

 

[Kenneth Hobson]

You can join discontinuous ranges like this too:

Dim BigRange As Range
Set BigRange = Range("F11:F24,F27:F35,F38:F41,F59:F68,F71:F81,F84:F93")

Did you need to set a link to the other files or did you just need the values?

You only have a few minutes to edit a post. That makes you think about what to say before you submit it. Even so, sometimes my caffeine hasn't kicked in yet...

 

[Kenneth Hobson]

For the link issue:

Sub test()
  Dim cell As Range, Path As String, i As Long, aLinks
  
  Path = ThisWorkbook.Path
  Set cell = Range("A4")
  
  Application.DisplayAlerts = False
  cell.Formula = "='" & Path & "\[" & "Year.xls]" & ActiveSheet.Name & "'!" & cell.Address
  Application.DisplayAlerts = True
End Sub