Check value exists update it or add it

lawlor101

New Member
Joined
Nov 2, 2020
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone, I have issue with VBA with For next loops and verifying data.

Right now this macro does 2/3 of what I want, but I cannot get it to add the row on the end if it does not find it.

My goal is to check wb2 column "k" values against wb1 column "k" values and update if its their, and add if its not.


Am I over complicating this? I could really use some help here. Thanks


VBA Code:
Public Sub Complete()
With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    End With
    
Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = Workbooks("data-first.xlsm")
    Set wb2 = Workbooks("data-second.xlsm")

Dim wb1s As Worksheet, wb2s As Worksheet
    Set wb1s = Workbooks("data-first.xlsm").Worksheets(1)
    Set wb2s = Workbooks("data-second.xlsm").Worksheets(1)

Dim q As Long



        For q = 3 To wb2s.Rows.Count
            LastRowUpdate = wb2s.Cells(wb2s.Rows.Count, "K").End(xlUp).Row
            For y = 3 To LastRowUpdate 'wb1 movement
                If wb2s.Cells(y, 11) = wb1s.Cells(q, 11) Then
                    For i = 1 To 30 ' For the rows
                        If wb2s.Cells(y, i).Value <> wb1s.Cells(q, i).Value Then
                            wb1s.Cells(q, i) = wb2s.Cells(y, i)
                        End If
                    Next i 'End of the rows sub
                ElseIf wb2s.Cells(y, 11) <> wb1s.Cells(q, 11) Then
                    LastRowMaster = wb1s.Cells(wb1s.Rows.Count, "K").End(xlUp).Row
                    For v = q To LastRowMaster
                        If wb2s.Cells(y, 11) = wb1s.Cells(v, 11) Then
                            For i = 1 To 30 ' For the rows
                                If wb2s.Cells(y, i).Value <> wb1s.Cells(v, i).Value Then
                                    wb1s.Cells(v, i) = wb2s.Cells(y, i)
                                    'here we can say add value to last column to show change. (same above)
                                End If
                            Next i 'End of the rows sub
                            Else:
                        End If
                    Next v
                    End If
                
                Next y
            Next q 'end wb2 movement
            
                
            
    With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    End With
End Sub
 

lawlor101

New Member
Joined
Nov 2, 2020
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Using your code from post #14. The file is identical in everyway as I said its been duplicated. to me the code looks exactly like it would work... hence the curiosity on my behalf as to why it is not
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,311
Office Version
  1. 365
Platform
  1. Windows
Definitely odd as it works quite happily for me.
Would you be able to share the workbook, or does it have confidential info?
 

lawlor101

New Member
Joined
Nov 2, 2020
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Unfortunately confidential information. However, I found the error. Some of the data was imported as text and some as number format. Was throwing out some errors so I fixed it and voila. Nothing wrong with the code, just gotta make sure now that the inputted data always follows the correct format 🥲🥲
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,311
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,080
Members
414,426
Latest member
fraru

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
Top