Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Related Question
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Related Question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input Box Related Question | Excel Programming | |||
Input Box Related Question | Excel Programming | |||
Looping Macro with input boxes all related to one number | Excel Programming | |||
I have a VB related question,... | Excel Programming | |||
Want to input one character to cause related string to display in. | Excel Worksheet Functions |