Is recserial declared as a Date? If not, what is it declared as?

--
Rick (MVP - Excel)


"tracktraining" wrote in message
...
I also tried this and also got an error:

error out at this line: recserial = Format(Left(SerialRng, 6), "@@/@@/@@")


my SerialRng is in this format when in the excel cell: 060308-001 (i.e.
mmddyy-###)



--- start code -----

dim recserial as date

With Worksheets("Complaint Log (2)")
Set SerialRng = .Range("D2", .cells(.Rows.count, "D").End(xlUp))
End With

For Each SerialRng In SerialRng.cells
recserial = Format(Left(SerialRng, 6), "@@/@@/@@")
If IsDate(recserial) Then
If recserial = ss And recserial <= es Then
SerialRng.EntireRow.Copy
Sheets("Data").Select
Sheets("Data").cells(Rows.count,
1).End(xlUp)(2).PasteSpecial Paste:=xlAll
End If
End If
Next SerialRng

---- end code


thanks for helping out thus far


--
Learning


"Rick Rothstein" wrote:

Here is another method you can use to convert your serial numbers into
real
dates...

MySerial = Left(Format(SerialRng, "@@/@@/@@"), 8)

This code line requires MySerial to be Dim'med as a Date (so that VB will
convert the String value on the right side into a real date. If you want
to
force this conversion (instead of allowing VB to do the conversion behind
the scenes), then use this...

MySerial = CDate(Left(Format(SerialRng, "@@/@@/@@"), 8))

--
Rick (MVP - Excel)


"tracktraining" wrote in
message
...
Hi Everyone,

I have a column that contains serial numbers in this format - mmddyy -
###
(i.e. 060308-001).
I would like to split out the first 6 digits and convert it into a date
(mm/dd/yy).
I tried to do this with the following code and it doesn't work:

'------start code

startserial = Format(Me.Start_Serial, "mm/dd/yy")
endserial = Format(Me.End_Serial, "mm/dd/yy")

For Each SerialRng In SerialRng.cells
myserial = Left(SerialRng.cells(SerialRng.row, 4), 6)
record_serial = Format(myserial, "mm/dd/yy")
On Error Goto Next SerialRng ***(see note below)
If record_serial = startserial And record_serial <= endserial Then
SerialRng.EntireRow.Copy
Sheets("Data").Select
cells(Rows.count, 1).End(xlUp)(2).Select
Selection.PasteSpecial Paste:=xlAll
End If
Next SerialRng

'---- end code

***here I would like for it to skip to the next SerialRng if cannot get
the
first 6 digit to convert into a date - sometimes the field may not be a
serial number and just some other ID number so it can't be converted
into
a
date.

Please help if possible.


Thank you!
tracktraining


--
Learning