help code add time!!!

Nguyen Anh Dung

Board Regular
Joined
Feb 28, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
i have code as below.
example G2=2019-12-02 09:50:57 G3=2019-12-02 09:50:59
then add 2019-12-02 09:50:58
but in this case G2=2019-12-02 09:50:59 G3=2019-12-02 09:51:01 then not add time 2019-12-02 09:51:00
VBA Code:
Sub CSVAmend2(FolderPath As String, FileName As String)
    Dim wb As Workbook, ws As Worksheet, rng As Range
    Dim rangeToChange As Range
    Set rangeToChange = Range("G:G")
    Dim i As Integer, STT As Integer
    STT = 1
    Set wb = Workbooks.Open(FolderPath & FileName)
    Set ws = wb.Sheets(1)
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    On Error Resume Next
     For i = 3 To 500
              If Second(Cells(i + 1, "G")) - Second(Cells(i, "G")) <= 1 Then
              wb.Worksheets(1).Columns("G:G").NumberFormat = "yyyy-mm-dd hh:mm:ss"
              wb.Worksheets(1).Columns("F:F").NumberFormat = "yyyy-mm-dd hh:mm:ss"
              Exit For
              End If
            Next i
       For i = 3 To 500
              If Second(Cells(i + 1, "G")) - Second(Cells(i, "G")) > 1 Then
            Rows(i + 1).Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Cells(i + 1, "G") = Cells(i, "G") + 1.15740767796524E-05
            Cells(i + 1, "F") = Cells(i, "F") + 1.15740767796524E-05
            Cells(i + 1, "B") = Cells(i, "B")
            Cells(i + 1, "C") = Cells(i, "C")
            Cells(i + 1, "D") = Cells(i, "D")
            Cells(i + 1, "E") = Cells(i, "E")
            wb.Worksheets(1).Columns("G:G").NumberFormat = "yyyy-mm-dd hh:mm:ss"
            wb.Worksheets(1).Columns("F:F").NumberFormat = "yyyy-mm-dd hh:mm:ss"
              
      
        End If
     Next i
    Range("A2:A" & Rows.Count).ClearContents 'Xóa So Thu Tu Cu
    For i = 2 To Range("G" & Rows.Count).End(xlUp).Row    'vong lap tu dòng 2 den dong cuoi
        If Range("G" & i).Value <> "" Then     'neu cot GB khác rong
            Range("A" & i).Value2 = STT          'dien so thu tu
            STT = STT + 1              'tang STT lên 1 cho ô ke tiep
        End If
    Next i
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = False
    wb.Close savechanges:=True
End Sub
 
Last edited by a moderator:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,214,585
Messages
6,120,399
Members
448,958
Latest member
Hat4Life

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top