Finding the nearest date in a column with reference to today

[lenare]

Hi everyone.

I am stuck trying to figure out a macro. Basically, I have a column of dates with details a few columns away. When I run the macro, I want it to
search for the closest (past) date with reference to the date when the macro was run.

For example, today is March 21, 2013, and I have March 1, 5, 10, 18, 22, 26 in the column of dates. When I run the macro I want it to generate the data from March 18 (being the closest date). Like the amount to the right of March 18. Afterwards, it would just move to the next worksheet and process the same set of commands. (the details will be shown on the first sheet, while the 2nd sheet until the nth sheet will contain all the data).

I have an ideas with the min function, where I'll subtract the column of dates to the date today, then see which one has the smallest positive (or negative) number. I have no idea how to actually translate this into code :| Please help!

P.S. The date isn't limited to just a month. It could be as long as 6 months or so. (not more than a year I think)

 

[MickG]

You code appears to loop through each sheet returning the Temp2 value/Related data (ref:- Dates from Column "H"), to sheet "Report" (ex:-Worksheet(1))
I've modified the "Rng" variable again . I think this is more what you want.
Check with the code I sent for the correct sheet and range.
Not quite sure why you placed your variables in the "General declarations", You'll see I've placed them within the code.
This worked in principle for me, although not having the actual data!!

[COLOR="Navy"]Sub[/COLOR] LoanBalance()
Dim ReportDate As Variant '[COLOR="Green"][B]starting point for the week of the Loan/Maturityreport[/B][/COLOR]
Dim EndReportDate As Date '[COLOR="Green"][B]uppper limit of the report[/B][/COLOR]
Dim ValueDate '[COLOR="Green"][B]date of transaction[/B][/COLOR]
Dim RepriceDate As Date '[COLOR="Green"][B]Reprice Date[/B][/COLOR]
Dim MaturityDate As Date '[COLOR="Green"][B]Maturity Date[/B][/COLOR]
Dim OutstandingBalance As Double '[COLOR="Green"][B]Outstanding Balance[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Cell [COLOR="Navy"]As[/COLOR] Range
Dim NumWS As Integer '[COLOR="Green"][B]worksheet number[/B][/COLOR]
Dim RowNumReport As Long '[COLOR="Green"][B]counting row number in the report page[/B][/COLOR]
Dim RowNumRepDate As Long '[COLOR="Green"][B]counting row number in the worksheet of eachcompany (i.e. company A, company B etc.)[/B][/COLOR]
Dim rng As Range, dn As Range '[COLOR="Green"][B]rng ~ reprice date used for loan balance[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp2 [COLOR="Navy"]As[/COLOR] Range
Dim omin As Integer '[COLOR="Green"][B]number of days[/B][/COLOR]
Dim temp As Integer '[COLOR="Green"][B]mg[/B][/COLOR]
ReportDate = Date
MsgBox ("Report of outstanding loan balances as of " & ReportDate)
NumWS = 2
RowNumRepDate = 14
RowNumReport = 5
[COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] NumWS <= Worksheets.Count
[COLOR="Navy"]With[/COLOR] Worksheets(NumWS)
    [COLOR="Navy"]Set[/COLOR] rng = .Range(.Range("H14"), .Range("H" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
MsgBox rng.Address(external:=True)
omin = 365
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] rng
    [COLOR="Navy"]If[/COLOR] Date - dn > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] (Date - dn) < omin [COLOR="Navy"]Then[/COLOR]
            omin = Date - dn
            [COLOR="Navy"]Set[/COLOR] temp2 = dn
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] dn
[COLOR="Navy"]If[/COLOR] temp2.Offset(0, 6) > 0 [COLOR="Navy"]Then[/COLOR]
    Worksheets("Report").Range("F" & RowNumReport) = temp2.Offset(0, 6) '[COLOR="Green"][B]Outstanding Balance[/B][/COLOR]
    Worksheets("Report").Range("A" & RowNumReport) = Worksheets(NumWS).Range("C12") '[COLOR="Green"][B]Ao[/B][/COLOR]
    Worksheets("Report").Range("B" & RowNumReport) = Worksheets(NumWS).Range("C2") '[COLOR="Green"][B]Group Name[/B][/COLOR]
    Worksheets("Report").Range("C" & RowNumReport) = Worksheets(NumWS).Range("C1") '[COLOR="Green"][B]Company Name[/B][/COLOR]
    Worksheets("Report").Range("D" & RowNumReport) = Worksheets(NumWS).Range("C3") '[COLOR="Green"][B]Facility Type[/B][/COLOR]
    Worksheets("Report").Range("E" & RowNumReport) = temp2.Offset(0, -3) '[COLOR="Green"][B]Current Rate[/B][/COLOR]
    RowNumReport = RowNumReport + 1
    temp = 1
    NumWS = NumWS + 1
    RowNumRepDate = 14
[COLOR="Navy"]Else[/COLOR]
    NumWS = NumWS + 1
    RowNumRepDate = 14
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Loop[/COLOR]
[COLOR="Navy"]If[/COLOR] Not temp = 1 [COLOR="Navy"]Then[/COLOR]
MsgBox "You have no outstanding loan balance as of " & ReportDate
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[lenare]

Hi Mick!

Using your code... when it reaches sheet 25 (the sheet with the template, but no data), it still presents me the error message type mismatch. It highlights the "If Date - dn > 0 Then" when debugged. If I remove the extra template sheets, it seems to work just fine (but would it be possible to still have them there?).

Also, I have another problem with values for the outstanding balance (using the temp.offset). For example: My outstanding balance for sheet 5 is 2,000,000.00. But in the next sheet, all my dns are larger than the date today. Supposedly, I would want to skip the process and just go to the next worksheet. Instead, it pushes through and pastes some data from sheet 6, and the outstanding balance on sheet 5 (I'm thinking it's because of the temp.offset value being the range from the previous sheet). As a result, it prints the 2,000,000.00 once again.

Thanks for your replies!

 

[MickG]

I've now reset "Temp2" to "nothing" (Not Set & No value) at each loop and if you last sheet is 25 the code should not pass that sheet number.
See remarks in code.
If you have further problems I suggest you post a sample WorkBook vis "Box.com" or similar

[COLOR="Navy"]Sub[/COLOR] MG26Mar38
[COLOR="Navy"]Sub[/COLOR] LoanBalance()
Dim ReportDate As Variant '[COLOR="Green"][B]starting point for the week of the Loan/Maturityreport[/B][/COLOR]
Dim EndReportDate As Date '[COLOR="Green"][B]uppper limit of the report[/B][/COLOR]
Dim ValueDate '[COLOR="Green"][B]date of transaction[/B][/COLOR]
Dim RepriceDate As Date '[COLOR="Green"][B]Reprice Date[/B][/COLOR]
Dim MaturityDate As Date '[COLOR="Green"][B]Maturity Date[/B][/COLOR]
Dim OutstandingBalance As Double '[COLOR="Green"][B]Outstanding Balance[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] Cell [COLOR="Navy"]As[/COLOR] Range
Dim NumWS As Integer '[COLOR="Green"][B]worksheet number[/B][/COLOR]
Dim RowNumReport As Long '[COLOR="Green"][B]counting row number in the report page[/B][/COLOR]
Dim RowNumRepDate As Long '[COLOR="Green"][B]counting row number in the worksheet of eachcompany (i.e. company A, company B etc.)[/B][/COLOR]
Dim rng As Range, dn As Range '[COLOR="Green"][B]rng ~ reprice date used for loan balance[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] temp2 [COLOR="Navy"]As[/COLOR] Range
Dim omin As Integer '[COLOR="Green"][B]number of days[/B][/COLOR]
Dim temp As Integer '[COLOR="Green"][B]mg[/B][/COLOR]
ReportDate = Date
MsgBox ("Report of outstanding loan balances as of " & ReportDate)
NumWS = 2
RowNumRepDate = 14
RowNumReport = 5
Do While NumWS <= Worksheets.Count And NumWS < 25 '[COLOR="Green"][B]Altered here !!![/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] temp2 = Nothing
[COLOR="Navy"]With[/COLOR] Worksheets(NumWS)
    [COLOR="Navy"]Set[/COLOR] rng = .Range(.Range("H14"), .Range("H" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
MsgBox rng.Address(External:=True)
omin = 365
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] rng
    [COLOR="Navy"]If[/COLOR] Date - dn > 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] (Date - dn) < omin [COLOR="Navy"]Then[/COLOR]
            omin = Date - dn
            [COLOR="Navy"]Set[/COLOR] temp2 = dn
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] dn
If Not temp2 Is Nothing Then '[COLOR="Green"][B]altered here!!![/B][/COLOR]
[COLOR="Navy"]If[/COLOR] temp2.Offset(0, 6) > 0 [COLOR="Navy"]Then[/COLOR]
    Worksheets("Report").Range("F" & RowNumReport) = temp2.Offset(0, 6) '[COLOR="Green"][B]Outstanding Balance[/B][/COLOR]
    Worksheets("Report").Range("A" & RowNumReport) = Worksheets(NumWS).Range("C12") '[COLOR="Green"][B]Ao[/B][/COLOR]
    Worksheets("Report").Range("B" & RowNumReport) = Worksheets(NumWS).Range("C2") '[COLOR="Green"][B]Group Name[/B][/COLOR]
    Worksheets("Report").Range("C" & RowNumReport) = Worksheets(NumWS).Range("C1") '[COLOR="Green"][B]Company Name[/B][/COLOR]
    Worksheets("Report").Range("D" & RowNumReport) = Worksheets(NumWS).Range("C3") '[COLOR="Green"][B]Facility Type[/B][/COLOR]
    Worksheets("Report").Range("E" & RowNumReport) = temp2.Offset(0, -3) '[COLOR="Green"][B]Current Rate[/B][/COLOR]
    Worksheets("Report").Range("g" & RowNumReport) = temp2.Address(External:=True)
    RowNumReport = RowNumReport + 1
    temp = 1
    '[COLOR="Green"][B]NumWS = NumWS + 1[/B][/COLOR]
    RowNumRepDate = 14
[COLOR="Navy"]End[/COLOR] If
End If '[COLOR="Green"][B]Altered here!!![/B][/COLOR]
    NumWS = NumWS + 1
    RowNumRepDate = 14
[COLOR="Navy"]Loop[/COLOR]
[COLOR="Navy"]If[/COLOR] Not temp = 1 [COLOR="Navy"]Then[/COLOR]
MsgBox "You have no outstanding loan balance as of " & ReportDate
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[lenare]

Hey Mick, thanks a lot! Will try this out soon. Will upload the excel file there as well. Sorry for the inconvenience!

 

[lenare]

It's working now Thanks a lot!