PDA

View Full Version : Excel VBA to compare two lists



ctutka
03-14-2016, 08:12 AM
EDIT: I cleaned up my code a lot and figured out my "object required" issue. Now the issue I've been running into is the code only seems to be hitting the IF statement to update the required cell once and I'm not really sure as to why that is.


Option Explicit

Sub Test_Merge()

' Copy sheet from the newest spreadsheet to the master file and then close that workbook
Workbooks.Open Filename:="C:\Users\cmtutk\Documents\ProjectDashboard\Work_Assignments_by_Person_Team. xls"
Sheets("Work_Assignments_by_Person_Team").Copy After:=Workbooks("ProjectsMasterFile.xls").Sheets(Sheets.Count)
Workbooks("Work_Assignments_by_Person_Team.xls").Close

' Declare Variables
Dim mRow As Integer
Dim nRow As Integer
Dim DelOldVar As Integer
Dim wsM As Worksheet
Dim wsN As Worksheet
Set wsM = Worksheets("MasterSheet")
Set wsN = Worksheets("Work_Assignments_by_Person_Team")
mRow = 2
nRow = 2
DelOldVar = 0

' Nested Do While Not Loops to update actual hours field to current values
wsM.Activate
Do While Not wsM.Range("A" & mRow).Value = ""
Do While Not wsN.Range("A" & nRow).Value = ""
wsN.Activate
wsN.Range("B" & nRow).Select
If wsM.Range("B" & mRow).Value = wsN.Range("B" & nRow) And wsM.Range("A" & mRow).Value = wsN.Range("A" & nRow) Then
wsM.Range("L" & mRow).Value = "It hit value update"
DelOldVar = 1
End If
nRow = nRow + 1
wsM.Range("M" & mRow).Value = "It hit New Row Update"
Loop
nRow = 2
mRow = mRow + 1
wsM.Range("N" & mRow).Value = "It Hit Master Row Update"
If DelOldVar <> 1 Then
wsM.Range("B" & mRow).EntireRow.Delete
DelOldVar = 0
End If
Loop
End Sub