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

Jaromh45

New Member
Joined
May 13, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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 NoChargeNameDOBFine TotalFine Suspended
CR49-12-0078DUIJohn Doe19981000500
CR49-12-0077Open ContJared Doe20001000750
CR49-12-0075DUIJosh Doe1987
CR49-12-0074Open ContJohnny Doe19621000
CR49-12-0073DUIJessica Doe20011000500

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 NoCR49-12-0075
ChargeDUI
NameJosh Doe
DOB1987
Fine Total1000
Fine Suspended500

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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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".

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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