Hello,
I am trying to create a code that would potentially look for duplicates between two workbooks. What the code is doing right now is looking at cell "C2" ("Umbrella") and looking at the other workbook(i.e. Workbook2) for the value in column C until the last row. If the values match, the code looks at all cells in rng1 (i.e. "D26" until last used row) and looks for duplicates in column D until last row of Workbook2 . If both conditions are true, it changes the table value to "ti- duplicate found" and moves on to check the next row and so on...
What I need my code to do: need to change this part of the code "wb.Worksheets("Sheet1").Cells(i, "C")" so that it looks at all the used rows in Column C for duplicates and not just one row(i.e. i). Same goes for "wb.Worksheets("Sheet1").Cells(i, "B")".
I would be happy to answer any questions you have about the code or further explanation on what I am trying to create. Thank you!
I am trying to create a code that would potentially look for duplicates between two workbooks. What the code is doing right now is looking at cell "C2" ("Umbrella") and looking at the other workbook(i.e. Workbook2) for the value in column C until the last row. If the values match, the code looks at all cells in rng1 (i.e. "D26" until last used row) and looks for duplicates in column D until last row of Workbook2 . If both conditions are true, it changes the table value to "ti- duplicate found" and moves on to check the next row and so on...
Code:
Public Sub TiDuplicates(ws As Worksheet)
Dim lastrow As Long
Dim pathname As String
Dim foldername As String
Dim wb As Workbook
Dim i As Long
Dim cell1 As Range
Dim rng1 As Range
pathname = Mapping.Range("Task_Repository_Path")
foldername = Mapping.Range("Task_Repository_File_Name")
Workbooks.Open pathname & foldername
Set wb = Workbooks(foldername)
lastrow = wb.Worksheets("Sheet1").Cells(Rows.count, "C").End(xlUp).row
Set table = ws.Range("Meeting_Notes").Offset(0, 1)
Set rng1 = ws.Range("Task_Name:D" & Cells(Rows.count, "D").End(xlUp).row)
While table.row < ws.Range("Takeaways").row
If LCase(table(1, 1).value) = "ti" Then
For i = 5 To lastrow
If ws.Range("Umbrella").Offset(0, 1) = wb.Worksheets("Sheet1").Cells(i, "C") Then
For Each cell1 In rng1
If cell1.value = wb.Worksheets("Sheet1").Cells(i, "B") Then
table.value = "ti- duplicate found"
End If
Next cell1
End If
Next i
End If
Set table = table.Offset(1, 0)
Wend
What I need my code to do: need to change this part of the code "wb.Worksheets("Sheet1").Cells(i, "C")" so that it looks at all the used rows in Column C for duplicates and not just one row(i.e. i). Same goes for "wb.Worksheets("Sheet1").Cells(i, "B")".
I would be happy to answer any questions you have about the code or further explanation on what I am trying to create. Thank you!