Hello,
I'm trying to convert all the UNIX format time from all the worksheets into human format.
For that, i'm serching by "time event" columns (that is not fixed, in one sheet can be D, in other can be F), and where it finds this column, it perform the conversion.
It's working ok but it deletes also the very next column.
For example, if my Time event is incolumn D, it deletes the column E that contains data.
If i delete the red lines, the macro it will not delete the next column, but it will convert the UNIX time into a fixe date
31.12.1969 0:00:00
which is not good.
Can you help me with this?
Thank you in advance
I'm trying to convert all the UNIX format time from all the worksheets into human format.
For that, i'm serching by "time event" columns (that is not fixed, in one sheet can be D, in other can be F), and where it finds this column, it perform the conversion.
It's working ok but it deletes also the very next column.
For example, if my Time event is incolumn D, it deletes the column E that contains data.
Code:
Sub ConvertDate() Dim i As Long
Dim wks As Worksheet
Dim rFind As Range
Dim rOut As Range
For i = 2 To ActiveWorkbook.Sheets.Count
Set wks = Sheets(i)
With wks
Set rFind = .Cells.Find(What:="Time event", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
If Not rFind Is Nothing Then
Set rOut = Range(rFind, .Cells(Rows.Count, rFind.Column).End(xlUp))[COLOR=#ff0000][B].Offset(, 1)[/B][/COLOR]
If rOut.Rows.Count > 1 Then
rOut.Cells(1) = "Time event"
With rOut.Offset(1)
.NumberFormat = "dd/mm/yyyy hh:mm:ss"
.FormulaR1C1 = "=IF(RC[-1] = """", """", RC[-1]/86400 + DATE(1970,1,1) - DATE(1900,1,1))"
.Value2 = .Value2
.EntireColumn.AutoFit
[COLOR=#ff0000][B] .Offset(, -1).EntireColumn.Delete[/B][/COLOR]
End With
End If
End If
End With
Next i
End Sub
If i delete the red lines, the macro it will not delete the next column, but it will convert the UNIX time into a fixe date
31.12.1969 0:00:00
which is not good.
Can you help me with this?
Thank you in advance