Solved

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

Posted on 2014-07-24
5
90 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 47

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 47

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 47

Expert Comment

by:Martin Liss
ID: 40217810
Your welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

726 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