Change Macro Formula

Hi,

I created macro named “Works” within the workbook “ContinueExample.xlsm” attached to this post that populates a Date Value in cell “P2” to the very last cell in Column P based on the last row with data in worksheet “strSName”.

The way the macro knows which Date Value to populate the “P” cells with is based on the values found in Column N beginning with cell “N2” in worksheet “strSName” and the range values beginning with M2 to the last cell in Column N in worksheet “strLName”.

Here is an example of how the macro works:
In cell “N2” you find the value “Honda” in worksheet “strSName”, what the macro will do is look up the value “Honda” beginning with cell “M2” in worksheet “strLName” and if it exists, it will populate cell “P2” in worksheet “strSName” with the corresponding Date Value which in this case would be “N2” of sheet “strLName”, but if it doesn’t exist it will leave the cell blank.

If you execute the macro it will do the same thing for the rest of the cells in Column N in worksheet “strSName” and then determine which Date Value to assign it in Column P cells.

The macro runs fine, but it is not flexible enough. What I mean is that the VLOOKUP formula that I am using right now within the VBA code is limited to 6 rows (not counting row 1 which contains row titles) which are based on the number of rows in worksheet “strLName”. For this simple example I obviously know the number of rows in worksheet “strLName”, but going forward this will not be the case. The number of rows within worksheet “strLName” can vary and I don’t want to manually change this every time I execute this macro. I need some help with my VLOOKUP formula found within the macro to account for this row change.

AlphaFrog,

That did the trick! Thank you for your time.

- BC