Solved

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

Posted on 2014-07-24
89 Views
Folks,
I'd like to check to see if a year has already been entered?
0
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
• 3
• 2

LVL 47

Expert Comment

ID: 40217748

``````    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
``````
0

LVL 47

Accepted Solution

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

Lines 7 and 16 to 21 are new.

``````Sub AddYear()

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
'    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
``````
0

Author Comment

ID: 40217768
Understood - thanks
0

Author Closing Comment

ID: 40217770
0

LVL 47

Expert Comment

ID: 40217810
0

## Featured Post

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.