Run VBA VLOOKUP Code only on Filtered/Visible Cells

Question

Sub ActivityMatching()

Worksheets("AuroraData").Activate
Set lookRange = Sheets("AuroraData").Range("A2:D1000")

Worksheets("PO List").Activate
ActiveSheet.Range("CD1").AutoFilter Field:=82, Criteria1:="Yes"     //set the filter to "Yes" in Col CD
LastRow = Sheets("PO List").Cells(Rows.Count, "AK").End(xlUp).Row

With Application

    For i = 3 To LastRow
            Worksheets("PO List").Cells(i, 52) = .VLookup((Worksheets("PO List").Cells(i, 37).Value & Worksheets("PO List").Cells(1, 52).Value), lookRange, 4, False)
    Next i

End With

Worksheets("PO List").Activate

End Sub

I am trying to use VBA code to do VLOOKUP across two sheets. If I run the code above, here is the result I get (in Column AZ).

The VLOOKUP part works. The problem is I only want to run VBA code on rows with a “Yes” value in Column CD. If a row has a “No” in column CD, I want the VBA code to skip it and don’t do anything (these rows are supposed to be filled manually, so I don’t want my VBA code to erase the existing data in these rows).

I can’t figure out how to do it…below is how I tried to use xlCellTypeVisible, but it didn’t work. I still got #N/A values in these “No” rows.

With Application
    For Each rw In filter_rng.SpecialCells(xlCellTypeVisible)
         For i = 3 To LastRow
            Worksheets("PO List").Cells(i, 52) = .VLookup((Worksheets("PO List").Cells(i, 37).Value & Worksheets("PO List").Cells(1, 52).Value), lookRange, 4, False)
        Next i
     Next rw

How should I edit my code so it can skip these rows with "No" in Column CD? Thanks in advance!

Answer 1

Check if column offsets and indexes are OK

Option Explicit

Sub ActivityMatching()

Dim wsToLook As Worksheet
Set wsToLook = ThisWorkbook.Sheets("AurorData")
Dim rngToLook As Range
Set rngToLook = wsToLook.Range("A2:D1000")

Dim wsMain As Worksheet
Set wsMain = ThisWorkbook.Sheets("PO List")

Dim iCell As Range
Dim rngToInsert As Range
Dim lastRow As Long
Dim whatToFind As Variant

    With wsMain

        .Range("A1:CD1").AutoFilter Field:=82, Criteria1:="Yes"    
        lastRow = .Cells(.Rows.Count, "AK").End(xlUp).Row

        Set rngToInsert = .Range("AZ3:AZ" & lastRow).SpecialCells(xlCellTypeVisible)

        For Each iCell In rngToInsert
            whatToFind = iCell.Offset(, -15).Value & .Cells(1, 52).Value
            iCell.Value = Application.VLookup(whatToFind, rngToLook, 4, False)
        Next iCell

    End With

End Sub

Problem in your code

For Each rw In filter_rng.SpecialCells(xlCellTypeVisible)
     For i = 3 To LastRow
          ' here you was iterating through every "i" row
          ' and you was doing that many times 
          ' equal to amount of "yes" in a range
          ' what makes no sense ;)
          ' filter_rng.SpecialCells(xlCellTypeVisible).Cells.Count * (lastRow - 2)

          Worksheets("PO List").Cells(i, 52) = something

     Next i
Next rw