Input Box Related Question

Let's try this again. I think the last post was just a subject.

I have the following code:

Private Sub CPIDateEntry()
Dim cpistart
Msgboxloc:
cpistart = InputBox("Enter START date for report PERIOD in MM-YYYY Format ")
Debug.Print cpistart
If cpistart <> "" Then
If IsDate(cpistart) Then
' MsgBox "Continue the macro"
' Selection.NumberFormat = "dd/mm/yyyy"
' ActiveCell.Offset(0, 2).Range("A1").Select
Sheets("CPI_SPI_PITD").Select
Range("B33").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(CPIStart),MONTH(CPIStart),1)"
Selection.NumberFormat = "mmm-yy"
Exit Sub
Range("B33").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
End If
Else
MsgBox "You did not enter a date"
GoTo Msgboxloc
End If
End Sub

WHY am I not getting anything displayed in B33 of CPI_SPI_PITD? It's not
recognizing CPIStart as anything. The error message is currently #NAME?

I'm sure I'm doing something basic wrong.

Thanks

It "throws up" before EXIT SUB because it doesn't know what CPIStart is for
some reason.

"Kevin B" wrote:

> Could it be the "Exit Sub" statement in the middle of the IF statement?
>
> See code snippet:
>
> > ActiveCell.FormulaR1C1 = "=DATE(YEAR(CPIStart),MONTH(CPIStart),1)"
> > Selection.NumberFormat = "mmm-yy"
> > Exit Sub
> > Range("B33").Select
>
> --
> Kevin Backmann
>
>
> "Barb Reinhardt" wrote:
>
> > Let's try this again. I think the last post was just a subject.
> >
> > I have the following code:
> >
> > Private Sub CPIDateEntry()
> > Dim cpistart
> > Msgboxloc:
> > cpistart = InputBox("Enter START date for report PERIOD in MM-YYYY Format ")
> > Debug.Print cpistart
> > If cpistart <> "" Then
> > If IsDate(cpistart) Then
> > ' MsgBox "Continue the macro"
> > ' Selection.NumberFormat = "dd/mm/yyyy"
> > ' ActiveCell.Offset(0, 2).Range("A1").Select
> > Sheets("CPI_SPI_PITD").Select
> > Range("B33").Select
> > ActiveCell.FormulaR1C1 = "=DATE(YEAR(CPIStart),MONTH(CPIStart),1)"
> > Selection.NumberFormat = "mmm-yy"
> > Exit Sub
> > Range("B33").Select
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Application.CutCopyMode = False
> > Calculate
> > End If
> > Else
> > MsgBox "You did not enter a date"
> > GoTo Msgboxloc
> > End If
> > End Sub
> >
> > WHY am I not getting anything displayed in B33 of CPI_SPI_PITD? It's not
> > recognizing CPIStart as anything. The error message is currently #NAME?
> >
> > I'm sure I'm doing something basic wrong.
> >
> > Thanks

It runs fine using this modified version
======================================================
Private Sub CPIDateEntry()

Dim cpistart
Dim wb As Workbook
Dim ws As Worksheet
Dim iYr As Integer
Dim iMonth As Integer
Dim strVAL As String

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("CPI_SPI_PITD")

Msgboxloc:
cpistart = InputBox("Enter START date for report PERIOD in MM-YYYY Format ")
Debug.Print cpistart
If cpistart <> "" Then
If IsDate(cpistart) Then
ws.Activate
Range("B33").Select
iMonth = Month(cpistart)
iYr = Year(cpistart)
strVAL = "=DATE(" & iYr & "," & iMonth & ",1)"

ActiveCell.FormulaR1C1 = strVAL
Selection.NumberFormat = "mmm-yy"
Exit Sub
Range("B33").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Calculate
End If
Else
MsgBox "You did not enter a date"
GoTo Msgboxloc
End If
End Sub
======================================================
--
Kevin Backmann


"Barb Reinhardt" wrote:

> Let's try this again. I think the last post was just a subject.
>
> I have the following code:
>
> Private Sub CPIDateEntry()
> Dim cpistart
> Msgboxloc:
> cpistart = InputBox("Enter START date for report PERIOD in MM-YYYY Format ")
> Debug.Print cpistart
> If cpistart <> "" Then
> If IsDate(cpistart) Then
> ' MsgBox "Continue the macro"
> ' Selection.NumberFormat = "dd/mm/yyyy"
> ' ActiveCell.Offset(0, 2).Range("A1").Select
> Sheets("CPI_SPI_PITD").Select
> Range("B33").Select
> ActiveCell.FormulaR1C1 = "=DATE(YEAR(CPIStart),MONTH(CPIStart),1)"
> Selection.NumberFormat = "mmm-yy"
> Exit Sub
> Range("B33").Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Calculate
> End If
> Else
> MsgBox "You did not enter a date"
> GoTo Msgboxloc
> End If
> End Sub
>
> WHY am I not getting anything displayed in B33 of CPI_SPI_PITD? It's not
> recognizing CPIStart as anything. The error message is currently #NAME?
>
> I'm sure I'm doing something basic wrong.
>
> Thanks