search compare columns sheet 1 & sheet 2 and change a cell in sheet 1

Question

I really need some help.

Problem: I have a workbook with 2 worksheets. Both sheets has headers. Sheet1 is a list of account numbers in column A and the same for sheet 2 column A. Now, what I need to do is this:

if I place a date in column AI in sheet 2 for a specific account number, then find the corresponding account number in sheet 1 and place the word "Complete" in column Y for that account.

I hope I explained this enough. Below is what I came up with so far, but got stuck:

Sub UpdateTBP()
Dim i
Dim j
Dim k
Dim LastRow
Dim LastRow2

LastRow = Sheets("Portfolio").Cells(Rows.Count, 1).End(xlUp).Row
LastRow2 = Sheets("TBP").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To (LastRow - 1)
  For j = 2 To (LastRow2 - 1)
    If Sheets("Portfolio").Cells(i, 1) = Sheets("TBP").Cells(j, 1).value Then
      For k = 35 To 35
        If Sheets("TBP").Cells(j, 35) <> "" Then
          Sheets("Portfolio").Cells(i, Y).value = "Complete"
        End If
      Next
    End If
  Next
Next
ThisWorkbook.Save
 End Sub

Did the code just not work, or you're not even sure how to do what you want to? — hypetech
Are you getting an error message, or is the data appearing but wrong, or is no data showing up? Please give all the details you have. — hypetech
@hypetech ...The code isn't working after the "For k = 35 to 35" statement. Before this code, I had another code developed in a similar way but it would only change the cell for that row. For an example: sheet 2 column A row 3 account number is found on sheet 1 column A row 5 but because the account number data is in row 3 column A of sheet 2, it only changed column Y of sheet 1 for row 3 and not row 5 (row 5 is the corresponding account number) — awilso11
@hypetech This is the first code I developed before posting this one as explained above: Sub UpdateTBP() Dim i Dim LastRow LastRow = Sheets("TBP").Range("A" & Rows.Count).End(xlUp).Row For i = 2 To LastRow If Sheets("TBP").Cells(i, "AI").value <> "" And Sheets("TBP").Cells(i, "A").value = Sheets("Portfolio").Cells(i, "A").value Then Sheets("Portfolio").Cells(i, "Y").value = "Complete" End If Next ThisWorkbook.Save End Sub — awilso11
@hypetech There's no error message or anything, its just that the word "Complete" is not being filled in at all (neither the correct or incorrect cells; as if nothing is being done) — awilso11

daytonrazorback daytonrazorback · Accepted Answer · 2015-06-17T20:06:50

I was able to make it work by using the following modified code:

Sub UpdateTBP()
Dim i
Dim j
Dim k
Dim LastRow
Dim LastRow2


LastRow = Sheets("Portfolio").Cells(Rows.Count, 1).End(xlUp).Row
LastRow2 = Sheets("TBP").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To (LastRow - 1)
  For j = 2 To (LastRow2 - 1)
    If Sheets("Portfolio").Cells(i, 1).Value = Sheets("TBP").Cells(j, 1).Value Then
        If Sheets("TBP").Cells(j, 35) <> "" Then
          Sheets("Portfolio").Cells(i, 25).Value = "Complete"
        End If
    End If
  Next
Next
ThisWorkbook.Save

End Sub

Please note that this will not include your last row of data as you have subtracted it out with:

For i = 2 To (LastRow - 1)
    For j = 2 To (LastRow2 - 1)

If you wish to include that last row, just use the following:

For i = 2 to LastRow
    For j = 2 to LastRow2

This includeds the previous comments I made. I just gave it a quick test and it is working.

search compare columns sheet 1 & sheet 2 and change a cell in sheet 1

1 Answers