I have the following code for finding a specific value in a range of
cells and then copying the found cells to new columns. It works very
well, but when it copies the date column it changes the dates from
dd/mm/yyyy to mm/dd/yyyy.

I have tried formating the cells but it does not work. VBA seems to
change them over somehow.

Any clues?

For each cell in range("b1:b1200")
if left (cell.value,1) = "S" then
nextrow = range("O65536").end(xlup).row + 1
cells(nextrow,15) = cell.offset(0, -1).value
cells(nextrow,16) = cell.offset(0, 0).value
end if
next cell


thanks,

See if this works

For Each cell In Range("b1:b1200")
If Left(cell.Value, 1) = "S" Then
nextrow = Range("O65536").End(xlUp).Row + 1
Cells(nextrow, 15) = CDate(cell.Offset(0, -1).Value)
Cells(nextrow, 16) = cell.Value
End If
Next cell


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mmmm" wrote in message
...
I have the following code for finding a specific value in a range of
cells and then copying the found cells to new columns. It works very
well, but when it copies the date column it changes the dates from
dd/mm/yyyy to mm/dd/yyyy.

I have tried formating the cells but it does not work. VBA seems to
change them over somehow.

Any clues?

For each cell in range("b1:b1200")
if left (cell.value,1) = "S" then
nextrow = range("O65536").end(xlup).row + 1
cells(nextrow,15) = cell.offset(0, -1).value
cells(nextrow,16) = cell.offset(0, 0).value
end if
next cell


thanks,

Thanks Bob,

Thats got it working fine.

thanks again.




On Sat, 1 Sep 2007 10:34:14 +0100, "Bob Phillips"
wrote:

See if this works

For Each cell In Range("b1:b1200")
If Left(cell.Value, 1) = "S" Then
nextrow = Range("O65536").End(xlUp).Row + 1
Cells(nextrow, 15) = CDate(cell.Offset(0, -1).Value)
Cells(nextrow, 16) = cell.Value
End If
Next cell