Dynamic file name and loop question

drno3228

New Member
Joined
Dec 11, 2014
Messages
12
Hey guys,

I'm trying to use the following code to open a file path name that changes based on month and year, and loops until it has completed the process for 12 months. One row per a month. Right now, the following code doesn't loop, and I receive a "Compile error: Variable not defined".

Code:
Sub ReinvestmentIncome_Check()

Dim wb As Workbook
Dim Lmonth As Date
Dim Smonth As Date
Dim Yr As Date
Dim Syr As Date

Lmonth = ActiveSheet.Range(A2).Value
Smonth = ActiveSheet.Range(B2).Value
Yr = ActiveSheet.Range(C2).Value
Syr = ActiveSheet.Range(D2).Value

Set wb = Workbooks.Open("J:\CF_NIM\" & Yr & " Fair Value Analysis\Monthly Fair Value Attribution\" & Lmonth & " " & Syr & "\" & Smonth & " " & Syr & " FV Return Estimation & Adj Detail.xls")
    
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("LC & Float").Select
    Windows("Nfloat Attribution History.xlsm").Activate
    ActiveCell.FormulaR1C1 = _
        "=-'[Feb 11 FV Return Estimation & Adj Detail.xls]LC & Float'!R62C12/10^6"
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=-SUM('[Feb 11 FV Return Estimation & Adj Detail.xls]LC & Float'!R62C12:R142C12)/10^6-RC[-25]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    
End Sub

Excel workbook that it will run in:

PeriodResidualReinvestment IncomeCoupon Income Check
JanuaryJan201111Jan-1128
FebruaryFeb201111Feb-11(6)
MarchMar201111Mar-1112
AprilApr201111Apr-1141
MayMay201111May-1136
JuneJun201111Jun-1111
JulyJul201111Jul-1118
AugustAug201111Aug-1112
SeptemberSep201111Sep-1112
OctoberOct201111Oct-1133
NovemberNov201111Nov-1197
DecemberDec201111Dec-1136

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,257
Messages
5,769,075
Members
425,515
Latest member
baltusf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top