Accounting Calendar Input Box

[UnsolvedCoding]

I am having a brain fry and was hoping for ideas on how to deal with this problem.

I get raw data from Access that ends up in Excel. Once in Excel it gets manipulated to create totals. So far no problem.

However, one of the tabs for the report is specific to the last accounting month. Because accounting calendars are not uniform I can't use a standard assumption about what happens when.

There needs to be some way for the user to select or enter the start and end date based on the accounting calander for the specific client.

This raises two problems -
First whats the best way to get the dates. Input box? Cells? Calendars?

Second - whats the best way to use that range to sort or filter the list of dates covering several years in column F?

 

[SkipVought]

Hi,

Does your company not have a database table that cotains the accounting calendar.

My company, for instance, has a database table that contains data for actual calendar, manufacturing days, and accounting days. Then you join date values in your tables to the calendar to get associated accounting date or manufacturing date.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Not that I know of but I will double check.

 

[UnsolvedCoding]

We do not have an electronic version of the accounting calander used. So instead this is what I came up with and it has been working so far.

The variables are declared as public in a different module, they are Start_Date, End_Date and Test_Date as Date and Test as string.
Test is a generic variable used repeatedly for random items.

Option Explicit

Sub Dates_For_Last_Accounting_Month()

A:

' Clear the variables
Start_Date = Empty
End_Date = Empty
Test_Date = Empty
Test = Empty

' Get the start date for the previous calendar month from the user
Test = InputBox("Please enter start date in mm/dd/yy format", "Previous Accounting Month", "Start date")

' If the start date is empty exit the sub
If Test = "" Then Exit Sub

' If the default value of the input box is present start over
If Test = "Start date" Then
MsgBox "Default value selected. Please try again."
GoTo A
End If

Start_Date = CDate(Test)

B:

Test = Empty

Test = InputBox("Please enter end date in mm/dd/yy format", "Previous Accounting Month", "End date")

' If the start date is empty exit the sub
If Test = "" Then Exit Sub

' If the default value of the input box is present start over
If Test = "End date" Then
MsgBox "Default value selected. Please try again."
GoTo B
End If

End_Date = CDate(Test)

' If the start date is older then the end date swap values
If End_Date < Start_Date Then

' Save the end date before it gets changed
Test_Date = End_Date

' End date gets the oldest value
End_Date = Start_Date

' The start date becomes the most current of the dates
Start_Date = Test

End If

' If the start date is more current then the end date
If Start_Date > End_Date Then

' Tell the user they screwed up
MsgBox "The start date is greater than the end date, please re-enter dates"

' Restart date sequence
GoTo A

End If

Check_ Dates

End Sub


Sub Check_ Dates()

' Give the column a header
Worksheets("Data_Combined").Range("O1").Value = "PAM"

' Set variable to first row
i = 2

Do

' Clear the variable for each pass
Test_Date = Empty

' Get the value to test
Test_Date = Trim(Worksheets("Data_Combined").Range("F" & i).Value)


' If the test is between the start and end dates put PAM in the column.
' PAM stands for Previous Accounting Month
If Test_Date >= Start_Date And Test_Date <= End_Date Then Worksheets("Data_Combined").Range("O" & i).Value = "PAM"

' Go to next row
i = i + 1

' If there are no more claim dates exit the loop
If Trim(Worksheets("Data_Combined").Range("F" & i).Value) = "" Then Exit Do

Loop

End Sub

 

[SkipVought]

Empty is the value of an unassigned variant.

A Date variable is a double precision, and should be assigned a numeric value or an actual date.

A String variable ought to be initialized to a zero length string or "".

Other than that, is there a question?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Unless VBA has a between statment I haven't found I think that will wrap it up.

 

[SkipVought]

if var >= a and var <= b then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}