VBA Multiple Criteria and VLoopUp

Hi Guys,

Im am trying to make a code that can help me automate the making of contracts. Im on the right track but it seems like it's still lacking a bit of code.

In plain English this is want my code to do:

1: Read the variable "AptCode" and check if is the C-column on sheet shTenants
2: If AptCode is found in in the C-column test if that same line has the "ACTIVE" status found in Column F in shTenants
3: If both of the conditions are met, being AptCode and Active, then VLookup and print Tenant_Name and FavColor to cell A5:G9
4: Loop for all matches

I would like the the code to return the following:

"Batman likes the color Black, and Barbie Girl likes the color Pink"

Problem no.1: Crocodile Dundee no longer lives in the apartment since the status has been marked as TERMINATED
Problem no.2: The code only writes the data of Batman and not of Barbie Girl. It lacks the loop.


Can you guys help me? This is my very first VBA project!

Here's my code:

Please Login or Register  to view this content.

Hi Jacob and welcome to the forum,

Here's a suggestion. The code is slightly different then your but I put some comments for your understanding.

Please Login or Register  to view this content.

Please Login or Register  to view this content.