How do I edit rows based on criteria from an different table.

[Jaromh45]

I am working on a VBA code to do a couple things. The first is to add a new row and add the information I put into a seperate list. I want it to do this unless the Case Number and the Charge is the same. If those two things are the same then I want to fill in the rest of that row with that additional data.
So this is the data set I am looking at:

Case No	Charge	Name	DOB	Fine Total	Fine Suspended
CR49-12-0078	DUI	John Doe	1998	1000	500
CR49-12-0077	Open Cont	Jared Doe	2000	1000	750
CR49-12-0075	DUI	Josh Doe	1987
CR49-12-0074	Open Cont	Johnny Doe	1962	1000
CR49-12-0073	DUI	Jessica Doe	2001	1000	500

Then I will have a seperate box on a different sheet with the place to input the information that you want to add or edit

Case No	CR49-12-0075
Charge	DUI
Name	Josh Doe
DOB	1987
Fine Total	1000
Fine Suspended	500

If there is a way to move the data from the bottom table to the top table in a new row, and if it matches another row then just edit that row with the additional information, that would be awesome. Help me out with the VBA code to do this. Thank you.

Cheers

 

[igold]

Hi Jaromh45, welcome to the MrExcel Forum,

I am thinking that these are Ranges and not Tables. That said, the code assumes that the top Range in your post is on "Sheet1" and bottom Range (the part you want to add or edit) of your post is on "Sheet2".

Sub MoveData()

    Dim arrMain, arrAdd, lRow As Long, lRow2 As Long, i As Long, c As Integer
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Worksheets("Sheet2")
    
    lRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    lRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    arrMain = ws1.Range("A2", "F" & lRow)
    arrAdd = ws2.Range("B1", "B" & lRow2)
    For i = 1 To UBound(arrMain)
        If arrAdd(1, 1) = arrMain(i, 1) And arrAdd(2, 1) = arrMain(i, 2) Then
            For c = 3 To 6
                arrMain(i, c) = arrAdd(c, 1)
            Next
            c = 1
        End If
        If c = 1 Then Exit For
    Next
    If i = UBound(arrMain) + 1 Then
        ws1.Range("A" & lRow + 1).Resize(, UBound(arrAdd)) = Application.Transpose(arrAdd)
        Exit Sub
    End If
    ws1.Range("A2").Resize(UBound(arrMain, 1), UBound(arrMain, 2)) = arrMain
    
End Sub