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

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

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

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

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

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)

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)