I found a line I had forgotten to delete later in the sub that was causing
the problem. Your "skinny down your code" advice was what I needed to hear.
Thanks again for all your help

"Dave Peterson" wrote:

You may want to skinny down your code in a test procedure to just the portion
that needs to be there.

Get it working there to see that helps with you real procedure. If you can't
get it working, post the version of that simplified code and explain what's
happening for you.

callbr549 wrote:

I forgot to declare dateval. However, when I do declare dateval as long I
still get the same result. I've gone over the steps of the code you posted
that worked and compared them to mine and I can't see a difference. I must be
overlooking something, but haven't found it yet. I will keep looking. Thanks
for your help so far.

"Dave Peterson" wrote:

How did you declare dateval? As a date?

This worked ok for me:

Dim DateVal As Long
Dim NextRow As Long
Dim ValDate As Date
Dim myShift As Long

myShift = 1

ValDate = DateSerial(2009, 3, 27) 'your calendar control

NextRow = 1
DateVal = CLng(ValDate)
ActiveSheet.Cells(NextRow, 14) = DateVal & myShift



callbr549 wrote:

This isn't working for me either. Here's the code I'm trying to make work:

dateval = CLng(valDate)
Cells(nextrow, 14) = dateval & Shift

...where valDate is a date from a calendar dialog, and Shift is an integer
from 1 to 3. When the code executes, the cell is getting populated with a
number in the format "3/27/20092" where I'm trying to get "398992". A
similar text operation gets done in another part of the spreadsheet with the
formula:

=IF(ISNUMBER(F57),CONCATENATE(B57,D57),"")

where B57 is the date and D57 is the Shift. This formula returns the format
"398992" even though B57 is in format mm/dd/yyyy.

"Dave Peterson" wrote:

msgbox clng(date) & 1



callbr549 wrote:

I want to convert a date value to the serial number for that date and store
it in a string variable for later manipulation. All I can get is the value
stored in a date format, for example "3/27/2009". The spreadsheet I'm
working on uses the Excel Concatenate function to add a suffix to a date, and
the date comes out in serial number format. So for example, adding "1" to
today's date, the resulting cell reads "398991", but when I try to do the
same thing in VBA I can only get the resulting string to be "3/24/20091".

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson