CODEWITHSUNDEEP
vbaexcel
epicUsername
epicUsername

Reputation: 1009

Updating fiscal year with a macro

I'm a relatively new programmer in VBA for Excel and looking for some help on a work-related project.

The issue at hand is with respect to designing an update routine for a budget tracking system that would update the date entries for any upcoming fiscal year, specified by the user in an input box:

Sub Putittogether()
On Error Resume Next

Dim xMonthNumber As Integer
Dim xMonthNumber2 As Integer
Dim xYearInput As Integer
Dim xCell As Range
Dim xCell2 As Range
Dim xMonthYear As Range
Dim xMonthNextYear As Range

Set xMonthYear = Range("B4:B12")
Set xMonthNextYear = Range("B1:B3")


xYearInput = InputBox("Please enter year:")


For Each xCell In xMonthYear
    xCell = xYearInput
        For xMonthNumber = 4 To 12
        Range("B" & xMonthNumber).NumberFormat = "General"
        Range("B" & xMonthNumber) = MonthName(xMonthNumber, True) & xCell
           For Each xCell2 In xMonthNextYear
            xCell2 = xYearInput + 1
                For xMonthNumber2 = 1 To 3
                Range("B" & xMonthNumber2).NumberFormat = "General"
                Range("B" & xMonthNumber2) = MonthName(xMonthNumber2, True) & xCell2
            Next xMonthNumber2
        Next xCell2
    Next xMonthNumber
Next xCell

End Sub

The macro goes from April of the current year to March of the following year, thus the double loops.

I run into issues in two places: using the method I'm using, the ranges in the B column need to be strictly reflected in rows 1-12 and any other rows causes problems with the output, and I'm not sure why.

The second issue is that I've got tracking totals tables for every month and trying to run this macro in a table that has a header row (i.e. shifts the first data row to B2) creates additional output problems - so, how do I make it work in a different range?

Thank you in advance for your help!!

Upvotes: 1

Views: 1187

Answers (2)

Ambie
Ambie

Reputation: 4977

Couldn't you use the Date data type and then the DateAdd() function. This would simplify your loop to just two lines of code.

If you need to stipulate a start row then one way would be to run the row loop from startRow to startRow + 11.

So your code would look something like this:

Const START_ROW As Integer = 1 'set this to the row you want
Dim ws As Worksheet
Dim r As Integer
Dim dat As Date
Dim yyyy As Integer

Set ws = ThisWorkbook.Worksheets("Sheet1")

yyyy = InputBox("Please enter year:")

dat = DateSerial(yyyy, 4, 1)
For r = START_ROW To START_ROW + 11
    ws.Cells(r, "B").Value = Format(dat, "mmmyyyy")
    dat = DateAdd("m", 1, dat)
Next

Upvotes: 1

INOPIAE
INOPIAE

Reputation: 293

May be you can use this code

Sub Putittogether()
   On Error Resume Next

   Dim xYearInput As Integer
   Dim xColumn As Integer
   Dim xDate As Date

   xYearInput = InputBox("Please enter year:")

   xDate = DateSerial(xYear,4,1)  'you might adjust this date to the first month to be entered

   For xColumn = 1 to 12

        Cells("B" & xColumn ).NumberFormat = "General"
        Cells("B" & xColumn ) = Format(xDate,"MMM YY")
        xDate = DateAdd("m", 1, xDate)

    Next xColumn 

End Sub

Upvotes: 1

Related Questions