how to look for a value and then place it in the next available cell?

I am working between two sheets. 1. Raw Data sheet 2. Tempe.

Could you please help me create a VB script that will look in the Raw data sheet column C for any value that says Tempe
Then check and see if the date in Raw Data Sheet Column D is within the same date range listed in the Tempe worksheet between Cells B2 and B738.
If the date is within range then find the value(Campaign name) in Raw Data Sheet, Column A and check if it has already exists in Tempe sheet Range F1:Q1. If it does not exist there already put the Value (Campaign name) posted in the Raw Data sheet and post it in the Tempe sheet Range F1:Q1 in the first available column.

I have attached the sheet I am working with for reference
I appreciate all the input here!
Abigail EvansWorkforce Capacity Plan AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dani DobreExcel Expert and Automation GeekCommented:
The code below loops all rows in Raw Data Sheet and for Site 'Tempe', looks up the date in Tempe sheet. If the date is found, then the campaign name is pasted in the next available column in Tempe, starting from column F.

Sub MatchCampaign()
    Dim wsTempe As Worksheet
    Dim wsRaw As Worksheet
    Dim lRawRow As Long
    Dim dStartRange As Date
    Dim dEndRange As Date
    Set wsTempe = ThisWorkbook.Sheets("Tempe")
    Set wsRaw = ThisWorkbook.Sheets("Raw Data Sheet")
    ' find last row
    lRawRow = wsRaw.Range("A:I").Find("*", , , , xlByRows, xlPrevious).Row
    ' loop all rows in Raw Data Sheet
    For xRow = 2 To lRawRow
        If wsRaw.Cells(xRow, "B").Value = "Tempe" Then
            dDate = wsRaw.Cells(xRow, "D").Value
            ' search date in Tempe
            Set oCell = wsTempe.Range("B:B").Find(dDate, , xlValues)
            If Not oCell Is Nothing Then
                ' if date was found, search for the next empty column
                Set oInsertCell = oCell.EntireRow.Find("*", , , , , xlPrevious).Offset(, 1)
                ' enter campaign name
                oInsertCell.Value = wsRaw.Cells(xRow, "A").Value
            End If
        End If
    Next xRow
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abigail EvansWorkforce Capacity Plan AnalystAuthor Commented:
Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.