convert unix time in human time

[tuspilica]

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.

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

 

[Tetra201]

You forgot to insert a column for your R1C1 formulas.

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
            [COLOR=#ff0000]Columns(rFind.Column + 1).EntireColumn.Insert[/COLOR]
            Set rOut = Range(rFind, .Cells(Rows.Count, rFind.Column).End(xlUp)).Offset(, 1)
            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
                    .Offset(, -1).EntireColumn.Delete
                End With
            End If
        End If
    End With
Next i
End Sub

 

[tuspilica]

Thank you Tetra201
The idea is very good and it works, somehow.
The issue is if i run the macro while i have the sheet with "Time event" opened, it is ok.
But, if i run the macro thru a button located on the 1st page, which is going to be the final design:

1. it introduce me a new column on the 1st sheet, what i don't want:

[COLOR=#333333] 
For i = 2 To ActiveWorkbook.Sheets.Count
[/COLOR][COLOR=#333333]    Set wks = Sheets(i)[/COLOR]

I want the macro to run (search and replace where exists the Time column) except the first one, that is protected and freezed (sheets 2 and 3 exists but are hidden).

2. the formula works, converts time, but it has the same behavior - not introducing the column on the sheets with Time, but only in the 1st one and "eats" the "Time+1" column.

 

[Tetra201]

Aah, the front dot got lost in action. Of course it should be .Columns(rFind.Column + 1).EntireColumn.Insert

 

[tuspilica]

the magic dot


thank you my friend
you made my day!
it works as i want

 

[Tetra201]

Glad to be of a help.