Convert CSE formula to a macro

I would prefer using a macro as I have most of the code now working except for this part and I am taking my data from an external Excel file.

To come to the end result, the code is going through the following steps:

1. Open outside data file (source workbook)
2. Copy rows which are part of a "block trade" to destination workbook
3. Lookup trade ticket number (sub TradeTicketNumber)
4. Close outside data file

It is the third step that I having trouble with the code. I am not sure if the way I approached this is the most effecient way to do this so if you have any comments on a different/more effective approach I would appreciate it.

Destination file and raw data file with full code attached if needed.

Please Login or Register  to view this content.

Shg,

I followed your advice and used the formulas you recommended. It is working outside the marcro but I am having trouble within the macro. I am using in a Marcro as follows:

Please Login or Register  to view this content.

The only issue I have is how to increment the data in column D. I tried using the following but it didn't work. Also, is it possible to pass a value instead of the formula?

Please Login or Register  to view this content.

After many hours of frustration, I finally figured out how to fix formula to include in a macro by searching many examples from this forum. Changed the formula from:

Please Login or Register  to view this content.

changed to

Please Login or Register  to view this content.

Thanks for original code.