Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass date to cell in spreadsheet
The following code Code: -------------------- CellPosition.Value = frmWorkHistory.txtStartMonth.Value) & "/31/" & frmWorkHistory.txtStartYear.Value -------------------- passes the value in text boxes txtStartMonth and StartYear to a cell in my spreadsheet I thought since the cell is formatted as date that the value in CellPosition.Value would display as a date but it's not can I use DateValue?? how can I pass a date to the cell in the spread sheet? Code: -------------------- Function StartDate() counter = 1 Set CellPosition = Range("C13") Do While counter <= 10 If CellPosition.Value = "" Then CellPosition.Value = frmWorkHistory.txtStartMonth.Value) & "/31/" & frmWorkHistory.txtStartYear.Value MsgBox CellPosition.Value Exit Do Else Set CellPosition = CellPosition.Offset(1, 0) counter = counter + 1 End If Loop End Function -------------------- -- cedtech23 ------------------------------------------------------------------------ cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022 View this thread: http://www.excelforum.com/showthread...hreadid=507527 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass date to cell in spreadsheet
Hi Cedtech23,
Try: CellPosition.Value = CDate(frmWorkHistory.txtStartMonth.Value _ & "/31/" & frmWorkHistory.txtStartYear.Value) --- Regards, Norman "cedtech23" wrote in message ... The following code Code: -------------------- CellPosition.Value = frmWorkHistory.txtStartMonth.Value) & "/31/" & frmWorkHistory.txtStartYear.Value -------------------- passes the value in text boxes txtStartMonth and StartYear to a cell in my spreadsheet I thought since the cell is formatted as date that the value in CellPosition.Value would display as a date but it's not can I use DateValue?? how can I pass a date to the cell in the spread sheet? Code: -------------------- Function StartDate() counter = 1 Set CellPosition = Range("C13") Do While counter <= 10 If CellPosition.Value = "" Then CellPosition.Value = frmWorkHistory.txtStartMonth.Value) & "/31/" & frmWorkHistory.txtStartYear.Value MsgBox CellPosition.Value Exit Do Else Set CellPosition = CellPosition.Offset(1, 0) counter = counter + 1 End If Loop End Function -------------------- -- cedtech23 ------------------------------------------------------------------------ cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022 View this thread: http://www.excelforum.com/showthread...hreadid=507527 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass date to cell in spreadsheet
I change the code to Code: -------------------- CellPosition.Value = CDate(frmWorkHistory.txtStartMonth.Value & "/31/" & frmWorkHistory.txtStartYear.Value) -------------------- and I got "run time error '13' type mismatch since the CDate function converts a value to a date. is it possible that "/31/" is causing this error?? -- cedtech23 ------------------------------------------------------------------------ cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022 View this thread: http://www.excelforum.com/showthread...hreadid=507527 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass date to cell in spreadsheet
I changed the code to Code: -------------------- Function StartDate() counter = 1 Set CellPosition = Range("C13") Do While counter <= 10 If CellPosition.Value = "" Then CellPosition.Value = CDate(frmWorkHistory.txtStartMonth.Value & "/1/" & frmWorkHistory.txtStartYear.Value) MsgBox CellPosition.Value Exit Do Else Set CellPosition = CellPosition.Offset(1, 0) counter = counter + 1 End If Loop End Function -------------------- it works but if I change "/1/" to "/31/" get "Run Time error '13' type mismatch" I don't understand why "/1/" works and "/31" does not?? -- cedtech23 ------------------------------------------------------------------------ cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022 View this thread: http://www.excelforum.com/showthread...hreadid=507527 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass date to cell in spreadsheet
Hi Cedtech31,
Try: With frmWorkHistory CellPosition.Value = DateSerial(.txtStartYear.Value, _ Me.txtStartMonth, 31) End With --- Regards, Norman "cedtech23" wrote in message ... I changed the code to Code: -------------------- Function StartDate() counter = 1 Set CellPosition = Range("C13") Do While counter <= 10 If CellPosition.Value = "" Then CellPosition.Value = CDate(frmWorkHistory.txtStartMonth.Value & "/1/" & frmWorkHistory.txtStartYear.Value) MsgBox CellPosition.Value Exit Do Else Set CellPosition = CellPosition.Offset(1, 0) counter = counter + 1 End If Loop End Function -------------------- it works but if I change "/1/" to "/31/" get "Run Time error '13' type mismatch" I don't understand why "/1/" works and "/31" does not?? -- cedtech23 ------------------------------------------------------------------------ cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022 View this thread: http://www.excelforum.com/showthread...hreadid=507527 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass date to cell in spreadsheet
Norman, your code will produce the wrong result if a month doesn't have 31 days. (iso Feb28 it'll give March3) following will give the last day of the month: with frmWorkHistory CellPosition.Value = _ DateSerial(.txtStartYear,.txtStartMonth+1,1)-1 end with -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Norman Jones wrote : With frmWorkHistory CellPosition.Value = DateSerial(.txtStartYear.Value, _ Me.txtStartMonth, 31) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass date to cell in spreadsheet
Hi KeepItCool,
your code will produce the wrong result if a month doesn't have 31 days. (iso Feb28 it'll give March3) following will give the last day of the month: with frmWorkHistory CellPosition.Value = _ DateSerial(.txtStartYear,.txtStartMonth+1,1)-1 end with True, but it is not clear that the OP necessarily wants the last day of the month - see his use of day 1 in earlier code. --- Regards, Norman "keepITcool" wrote in message .com... Norman, your code will produce the wrong result if a month doesn't have 31 days. (iso Feb28 it'll give March3) following will give the last day of the month: with frmWorkHistory CellPosition.Value = _ DateSerial(.txtStartYear,.txtStartMonth+1,1)-1 end with -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Norman Jones wrote : With frmWorkHistory CellPosition.Value = DateSerial(.txtStartYear.Value, _ Me.txtStartMonth, 31) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I pass network logon name to a cell in a spreadsheet? | Excel Worksheet Functions | |||
How to calculate pass/fail percentages entered on a spreadsheet? | New Users to Excel | |||
How do I flag a cell in Excel 2002 when the date entered has pass | Excel Discussion (Misc queries) | |||
How do I flag a cell in Excel 2002 when the date entered has pass | Excel Discussion (Misc queries) | |||
Pass file name from Spreadsheet Range | Excel Programming |