InsertUpdateLoad VBA

Function CekIdTransaksi(nomorid As Range) As Boolean
    Dim idtransaksi As String
    idtransaksi = nomorid.Value
    If idtransaksi <> "" Then
        CekIdTransaksi = True
    Else
        CekIdTransaksi = False
    End If
End Function


Sub SimpanNoCek(ByVal dana As Range, ByVal pencairan As Range, ByVal nomorcek As Range, ByVal tanggalcek As Range, ByVal nilai As Range, ByVal keterangan As Range, ByVal nomorkode As Range, ByVal tabeltujuan As ListObject)
    If CekIdTransaksi(nomorkode) = True Then
        Dim ceknumber As String
        ceknumber = nomorcek.Value
        If ceknumber = vbNullString Then
            MsgBox ("Nomor Cek belum diisi")
            nomorcek.Select
            Exit Sub
        Else
            If IsError(Application.Match(nomorkode.Value, tabeltujuan.ListColumns("KODE").Range, 0)) Then
                With tabeltujuan.ListRows.Add
                    .Range(1) = nomorkode
                    .Range(2) = dana.Value
                    .Range(3) = pencairan.Value
                    .Range(4) = nomorcek.Value
                    .Range(5) = tanggalcek.Value
                    .Range(6) = nilai.Value
                    .Range(7) = keterangan.Value
                End With
            Else
                For i = 1 To tabeltujuan.ListRows.Count
                    If tabeltujuan.ListRows(i).Range(1) = nomorkode Then
                        With tabeltujuan.ListRows(i)
                            .Range(4) = nomorcek.Value
                            .Range(5) = tanggalcek.Value
                            .Range(6) = nilai.Value
                            .Range(7) = keterangan.Value
                        End With
                    End If
                Next i
            End If
        End If
    Else
        MsgBox ("Dana atau periode pencairan belum diisi")
        nomorid.Select
        Exit Sub
    End If
End Sub

Sub LoadDataCek(ByVal kode As Range, ByVal tabelsumber As ListObject, ByRef nocek As Range, ByRef tglcek As Range, ByRef nilai As Range, ByRef ket As Range)
    If CekIdTransaksi(kode) = True Then
        nocek.Value = ""
        tglcek.Value = ""
        nilai.Value = ""
        ket.Value = ""
        If Not tabelsumber.DataBodyRange Is Nothing Then
            For i = 1 To tabelsumber.ListRows.Count
                If tabelsumber.ListRows(i).Range(1) = kode.Value Then
                    With tabelsumber.ListRows(i)
                        nocek.Value = .Range(4)
                        tglcek.Value = .Range(5)
                        nilai.Value = .Range(6)
                        ket.Value = .Range(7)
                    End With
                    Exit For
                End If
            Next i
        Else
            MsgBox ("Data Cek bank belum ada")
            Exit Sub
        End If
    End If
End Sub

Sub SimpanDataTransaksi(ByVal asal As ListObject, ByVal target As ListObject, ByVal kode As Range)
    Dim tabelasal As ListObject
    Set tabelasal = asal
    Dim tabeltarget As ListObject
    Set tabeltarget = target

    Dim kwit As String
    kwit = kode.Value

    If IsError(Application.Match(kwit, tabeltarget.ListColumns("KODE").Range, 0)) Then
        'MsgBox ("Data belum ada, data kwit ditambahkan")
        For i = 1 To tabelasal.ListRows.Count
            If tabelasal.ListRows(i).Range(1) = vbNullString Then Exit Sub
            If IsError(Application.Match(tabelasal.ListRows(i).Range(1), tabeltarget.ListColumns("ID").Range, 0)) Then
                'MsgBox ("data kwit belum ada, Data ID belum ada")
                Dim newrow As ListRow
                Set newrow = tabeltarget.ListRows.Add
                With newrow
                    .Range(1) = tabelasal.ListRows(i).Range(1)
                    .Range(2) = kwit
                    .Range(3) = tabelasal.ListRows(i).Range(3)
                    .Range(4) = tabelasal.ListRows(i).Range(4)
                    .Range(5) = tabelasal.ListRows(i).Range(5)
                    .Range(6) = tabelasal.ListRows(i).Range(6)
                    .Range(7) = tabelasal.ListRows(i).Range(7)
                    .Range(8) = tabelasal.ListRows(i).Range(8)
                    .Range(9) = tabelasal.ListRows(i).Range(9)
                    .Range(10) = tabelasal.ListRows(i).Range(10)
                    .Range(11) = tabelasal.ListRows(i).Range(11)
                    .Range(12) = tabelasal.ListRows(i).Range(12)
                    .Range(13) = tabelasal.ListRows(i).Range(13)
                    .Range(14) = tabelasal.ListRows(i).Range(14)
                    .Range(15) = tabelasal.ListRows(i).Range(15)
                    .Range(16) = tabelasal.ListRows(i).Range(16)
                End With
            Else
               'MsgBox ("data kwit belum ada, Data id sudah ada")
               Exit Sub
            End If
        Next i
    Else
        'MsgBox ("Data Kwit sudah ada, tambahkan data ID")
        For l = 1 To tabelasal.ListRows.Count
            If IsError(Application.Match(tabelasal.ListRows(l).Range(1), tabeltarget.ListColumns("ID").Range, 0)) Then
                MsgBox ("data kwit sudah ada, Data id belum ada")
                Dim nexrow As ListRow
                Set nexrow = tabeltarget.ListRows.Add
                With nexrow
                    .Range(1) = tabelasal.ListRows(l).Range(1)
                    .Range(2) = kwit
                    .Range(3) = tabelasal.ListRows(l).Range(3)
                    .Range(4) = tabelasal.ListRows(l).Range(4)
                    .Range(5) = tabelasal.ListRows(l).Range(5)
                    .Range(6) = tabelasal.ListRows(l).Range(6)
                    .Range(7) = tabelasal.ListRows(l).Range(7)
                    .Range(8) = tabelasal.ListRows(l).Range(8)
                    .Range(9) = tabelasal.ListRows(l).Range(9)
                    .Range(10) = tabelasal.ListRows(l).Range(10)
                    .Range(11) = tabelasal.ListRows(l).Range(11)
                    .Range(12) = tabelasal.ListRows(l).Range(12)
                    .Range(13) = tabelasal.ListRows(l).Range(13)
                    .Range(14) = tabelasal.ListRows(l).Range(14)
                    .Range(15) = tabelasal.ListRows(l).Range(15)
                    .Range(16) = tabelasal.ListRows(l).Range(16)
                End With

            Else
            'MsgBox (" Data kwit sudah ada, Data id sudah ada")
            For j = 1 To tabeltarget.ListRows.Count
                If tabelasal.ListRows(l).Range(1) = tabeltarget.ListRows(j).Range(1) Then
                    With tabeltarget.ListRows(j)
                        .Range(3) = tabelasal.ListRows(l).Range(3)
                        .Range(4) = tabelasal.ListRows(l).Range(4)
                        .Range(5) = tabelasal.ListRows(l).Range(5)
                        .Range(6) = tabelasal.ListRows(l).Range(6)
                        .Range(7) = tabelasal.ListRows(l).Range(7)
                        .Range(8) = tabelasal.ListRows(l).Range(8)
                        .Range(9) = tabelasal.ListRows(l).Range(9)
                        .Range(10) = tabelasal.ListRows(l).Range(10)
                        .Range(11) = tabelasal.ListRows(l).Range(11)
                        .Range(12) = tabelasal.ListRows(l).Range(12)
                        .Range(13) = tabelasal.ListRows(l).Range(13)
                        .Range(14) = tabelasal.ListRows(l).Range(14)
                        .Range(15) = tabelasal.ListRows(l).Range(15)
                        .Range(16) = tabelasal.ListRows(l).Range(16)
                        End With
                    Exit For
                End If
               Next j
            End If
        Next l
    End If

'cleanup
    For k = 1 To tabelasal.ListRows.Count
        If tabelasal.ListRows(k).Range(4) = vbNullString Then
            Dim rowtodelete As Long
            rowtodelete = Application.Match(tabelasal.ListRows(k).Range(1), tabeltarget.ListColumns("ID").Range, 0)
            MsgBox (rowtodelete)
            If rowtodelete > 1 Then
                tabeltarget.ListRows(rowtodelete - 1).Delete
            End If
        End If
    Next k

    tabeltarget.Sort.SortFields.Clear
    tabeltarget.Sort.SortFields.Add2 _
        Key:=tabeltarget.ListColumns(1).Range, SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers
    With tabeltarget.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

Sub LoadDataTransaksi(ByVal asal As ListObject, ByVal target As ListObject, ByVal id As Range)


    Dim tabelasal As ListObject
    Set tabelasal = asal
    Dim tabeltarget As ListObject
    Set tabeltarget = target

    Application.ScreenUpdating = False

    'If Not Intersect(Target, Range("D1")) Is Nothing Then
        If id.Value <> "" Then

            If Not tabelasal.DataBodyRange Is Nothing Then
                tabelasal.DataBodyRange.Delete
            End If

            Dim cekrow, jumlahrow, totalrow As Long
            If Not IsError(Application.Match(id.Value, tabeltarget.ListColumns("KODE").Range, 0)) Then
                cekrow = Application.Match(id.Value, tabeltarget.ListColumns("KODE").Range, 0) - 1
                jumlahrow = Application.CountIf(tabeltarget.ListColumns("KODE").Range, id.Value)
                totalrow = cekrow + jumlahrow - 1
            Else
                Exit Sub
            End If

            For i = cekrow To totalrow
            'For i = 1 To tabeltarget.ListRows.Count
                'MsgBox (i)
                If tabeltarget.ListRows(i).Range(2) = id.Value Then
                    With tabelasal.ListRows.Add
                        .Range(1) = tabeltarget.ListRows(i).Range(1)
                        .Range(2) = tabeltarget.ListRows(i).Range(2)
                        .Range(3) = tabeltarget.ListRows(i).Range(3)
                        .Range(4) = tabeltarget.ListRows(i).Range(4)
                        .Range(5) = tabeltarget.ListRows(i).Range(5)
                        .Range(6) = tabeltarget.ListRows(i).Range(6)
                        .Range(7) = tabeltarget.ListRows(i).Range(7)
                        .Range(8) = tabeltarget.ListRows(i).Range(8)
                        .Range(9) = tabeltarget.ListRows(i).Range(9)
                        .Range(10) = tabeltarget.ListRows(i).Range(10)
                        .Range(11) = tabeltarget.ListRows(i).Range(11)
                        .Range(12) = tabeltarget.ListRows(i).Range(12)
                        .Range(13) = tabeltarget.ListRows(i).Range(13)
                        .Range(14) = tabeltarget.ListRows(i).Range(14)
                        .Range(15) = tabeltarget.ListRows(i).Range(15)
                        .Range(16) = tabeltarget.ListRows(i).Range(16)
                    End With
                End If
            Next i
        End If
    'End If
    Application.ScreenUpdating = True

End Sub