Input Box Related Question

[Guest]

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

 

[Guest]

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

 

[Guest]

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
======================================================