Array Convert Pasted Range to Dates without looping

[neodjandre]

I am using this code which works fine:

Set copyrng = lo_b1.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
copyrng.Copy Destination:=strng
arr = strng.CurrentRegion.Offset(1, 0)
aRws = Evaluate("Row(1:" & UBound(arr) & ")")
arr = Application.Index(arr, aRws, Array(14, 1, 6, 2, 13, 12, 18, 16, 7))

pasterange1.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr

Set copyrng = Nothing
Erase arr
Erase aRws

The only problem I am having is when I am pasting the array at the end, some of the dates (column2 in the array) appear as text. I have to click on each cell and press enter to be converted to dates. Is there a way to paste as dates from the array?

What also puzzles me is that some entries are correctly shown as dates but most of them are text. I would also avoid looping in the array, as I have hundreds of entries and this will make things unusable.

many thanks!

 

[DanteAmor]

Try this:

change

[COLOR=#333333]pasterange1.Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr[/COLOR]

by

With pasterange1.Resize(UBound(arr, 1), UBound(arr, 2))
    .Value = arr
    .NumberFormat = "dd/mm/yyyy"
    .Value = .Value
End With

 

[neodjandre]

this works, but also converts the amount column to dates.. is it possible to restrict this to column(2) of the array please?

 

[neodjandre]

also the code errors out on negative amounts..

 

[DanteAmor]

Try this

With pasterange1.Resize(UBound(arr, 1), UBound(arr, 2))
    .Value = arr
    pasterange1.Resize(UBound(arr, 1)).NumberFormat = "General"
    pasterange1.Offset(, 1).Resize(UBound(arr, 1)).NumberFormat = "dd/mm/yyyy"
    .Value = .Value
End With