Solved

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

Posted on 2014-07-24
5
89 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

733 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