Pass date to cell in spreadsheet

The following code

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

Hi Cedtech23,

Try:

CellPosition.Value = CDate(frmWorkHistory.txtStartMonth.Value _
& "/31/" & frmWorkHistory.txtStartYear.Value)


---
Regards,
Norman



"cedtech23" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

I change the code to

Please Login or Register  to view this content.

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

I changed the code to

Please Login or Register  to view this content.

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

Hi Cedtech31,

Try:

With frmWorkHistory
CellPosition.Value = DateSerial(.txtStartYear.Value, _
Me.txtStartMonth, 31)
End With


---
Regards,
Norman



"cedtech23" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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)

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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)