Solved

check to see if any year entered already exists in the workbook

Posted on 2014-07-24
5
86 Views
Last Modified: 2014-07-24
Folks,
I'd like to check to see if a year has already been entered?
0
Comment
Question by:Frank Freese
  • 3
  • 2
5 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40217748
In the AddYear macro, after the Yearly value is entered add

    Dim sht As Worksheet
    For Each sht In Worksheets
        If InStr(1, sht.Name, Right$(Yearly, 2)) > 0 Then
            MsgBox "Tabs for the year " & Yearly & " already exist in the workbook. Please delete them and/or try again"
            Exit Sub
        End If
    Next

Open in new window

0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40217757
In other words the following.

Lines 7 and 16 to 21 are new.

Sub AddYear()

  ' add year
    Dim Yearly As String
    Dim lngIndex As Long
    Dim strYr As String
    Dim sht As Worksheet
    
    Yearly = InputBox("Please enter a 4 digit year to append to the added rows")
'    If Yearly < "2014" Or Yearly > "2050" Then
    If Yearly < Year(Now) Or Yearly > Year(Now) + 2 Then
        MsgBox "Invalid Year please try again"
        Exit Sub
    End If
    
    For Each sht In Worksheets
        If InStr(1, sht.Name, Right$(Yearly, 2)) > 0 Then
            MsgBox "Tabs for the year " & Yearly & " already exist in the workbook. Please delete them and/or try again"
            Exit Sub
        End If
    Next
    
    Range("B6:B17").Select
    Selection.NumberFormat = "General"
    Selection.Value = Yearly
    strYr = " " & Right$(Yearly, 2)
    gstrYear = Right$(Yearly, 2)
    'Append worksheet
    Append
    ReturnMenu
'    UpdateFormulas

    For lngIndex = 1 To 12
        ' MonthName(lngIndex, True) returns "Jan" when lngIndex is 1
        Range("C" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!B37"
        Range("D" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!C37"
        Range("E" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!D37"
        Range("F" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!E37"
        Range("G" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!F37"
        Range("H" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!H37"
        Range("I" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!I37"
        Range("J" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!J37"
        
        Range("K" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!K37"
        Range("L" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!L37"
        Range("M" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!M37"
        Range("N" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!N37"
    
        Range("O" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!O37"
        Range("P" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!P37"
        Range("Q" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!Q37"
        Range("R" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!R37"
    
        Range("S" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!S37"
        Range("T" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!T37"
        ' I added this line. Previously there was no formula for "Non-Billable"
        Range("U" & lngIndex + 5).Formula = "='" & MonthName(lngIndex, True) & strYr & "'!U37"
    Next

    MsgBox "Completed"
    Range("A1").Select
    End Sub

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 40217768
Understood - thanks
0
 

Author Closing Comment

by:Frank Freese
ID: 40217770
Great addition - thank you
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40217810
Your welcome.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question