1

I have a column of cells in one worksheet I want to verify against a column in another worksheet.
If there is a match, then I would like to add a comment.
However, when I try running the code, it does not add the comment as intended.
Comments highly appreciated!

Sub Checktabfour()

Dim i As Long
Dim j As Long
Dim k As Long
j = Sheets(5).Range("C" & Rows.Count).End(xlUp).Row
k = Sheets(4).Range("B" & Rows.Count).End(xlUp).Row

For i = 9 To k
If Cells(i, "B").Value <> "" And Cells(i, "B").Value = Sheets(5).Range("C" & j).Value Then
Cells(i, "D").Value = "Yes"

End If
Next i
End Sub
Jeremy
  • 13
  • 3
  • Statements like `Cells(i, "D").Value` are refers to the ActiveSheet. Use explicit refers like `Workbooks(1).Sheets(4).Cells(i, "D").Value` to be shure what sheet you want to be processed. Also see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Алексей Р Jun 02 '21 at 04:34
  • `I want to verify against a column in another worksheet` You say against a column but I see you are comparing every cell in column B in your range with only the last cell in Col C? Also avoid using `Sheets(4)` or `Sheets(5)`. Either use their codenames or the sheet names. For example `Sheet1.Range.....` or `Sheets("Sheet1").Range....` – Siddharth Rout Jun 02 '21 at 05:34

1 Answers1

0

Add Comment If a Match (For Each ... Next, Application.Match)

  • This is a 'range study', it surely can be improved using arrays.
  • Adjust C2 (the Source Worksheet first row is unknown) appropriately.
Option Explicit

Sub CheckFourAgainstFive()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets(5)
    Dim slCell As Range: Set slCell = sws.Range("C" & sws.Rows.Count).End(xlUp)
    Dim srg As Range: Set srg = sws.Range("C2", slCell)
    
    Dim dws As Worksheet: Set dws = wb.Sheets(4)
    Dim dlCell As Range: Set dlCell = dws.Range("B" & dws.Rows.Count).End(xlUp)
    Dim drg As Range: Set drg = dws.Range("B9", dlCell)
 
    Dim dCell As Range
    Dim cValue As Variant

    For Each dCell In drg.Cells
        cValue = dCell.Value
        If Not IsError(cValue) Then
            If Len(cValue) > 0 Then
                If IsNumeric(Application.Match(cValue, srg, 0)) Then
                    dCell.EntireRow.Columns("D").Value = "Yes"
                End If
            End If
        End If
    Next dCell
    
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Many thanks! What if I want to add the corresponding value from sheet 5 to sheet 4? – Jeremy Jun 02 '21 at 07:26
  • Which column in `Sheet5`? I'm assuming instead of `Yes`? For Column `E` in `Sheet5` it would be something like `Dim cIndex As Variant : cIndex = Application.Match(cValue, srg, 0) : If IsNumeric(cIndex) Then : dCell.EntireRow.Columns("D").Value = srg.Cells(cIndex).EntireRow.Columns("E").Value : End If`. – VBasic2008 Jun 02 '21 at 08:02
  • I have added the following code but it seems to replace my Sheet 5 Column D with blank values. ```Dim srg2 As Range: Set srg2 = sws.Range("C2", sws.Range("C" & sws.Rows.Count).End(xlUp)) : If IsNumeric(Application.Match(sValue, drg, 0)) : Then sCell.EntireRow.Columns("D").Value = drg.Cells(cIndex).EntireRow.Columns("C").Value``` – Jeremy Jun 02 '21 at 08:55
  • Your previous comment basically says that you want to copy something from Sheet5 to Sheet4 yet your code in this comment writes from Sheet4 to Sheet5. Also, you have introduced `sCell`!? Think about it for a while. Compare your and my 'proposal' and you'll note the difference. – VBasic2008 Jun 02 '21 at 09:43
  • Yes, that line of code works great. I also needed to copy from Sheet 4. Thinking that it works similarly, I thought the reverse is possible. Anyways, thank you very much for your help thus far. – Jeremy Jun 02 '21 at 10:47