Excel VBA Lookup values to populate matching columns

[sarge_leo]

I'm trying to lookup values from Master Data worksheet check if "CONDITION 1" is true and to populate with "Value" each column with header "SITE # 1" in Project Visit worksheet having empty row using VBA.
Note that number of row and columns will remain variable, hence I'm want to use <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: 13px; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; background-color: rgb(239, 240, 241); white-space: pre-wrap;">.UsedRange</code> to cut down unnecessary loops.

MASTER DATA

PROJECT VISIT

 

[sarge_leo]

Note that I'm trying to accomplish through vba, any suggestion / code would be appreciated.

 

[mumps]

With the MASTER DATA sheet being the active sheet, try this macro:

Sub copyValue()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundUnit As Range, foundSite As Range
    Dim sAddr As String
    For Each rng In Range("C2:C" & LastRow)
        If rng = "True" Then
            Set foundUnit = Sheets("PROJECT VISIT").Range("A:A").Find(rng.Offset(0, -2), LookIn:=xlValues, lookat:=xlWhole)
            Set foundSite = Sheets("PROJECT VISIT").Rows(2).Find("SITE # 1", LookIn:=xlValues, lookat:=xlWhole)
            If Not foundSite Is Nothing Then
                sAddr = foundSite.Address
                Do
                    If Sheets("PROJECT VISIT").Cells(foundUnit.Row, foundSite.Column) = "" Then
                        Sheets("PROJECT VISIT").Cells(foundUnit.Row, foundSite.Column) = rng.Offset(0, -1)
                        Exit Do
                    End If
                    Set foundSite = Sheets("PROJECT VISIT").Rows(2).FindNext(foundSite)
                Loop While foundSite.Address <> sAddr
                sAddr = ""
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub