Nguyen Anh Dung
Board Regular
- Joined
- Feb 28, 2020
- Messages
- 180
- Office Version
- 2016
- Platform
- 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
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: