Macro for procuring values from a dataset using different matching criteria

Hi Team,

I am new to the forum as well as VBA programming.

I am recording and editing steps in order to procure unique ID’s from a dataset and eliminate the manual intervention for this process. Macro works seamlessly in most of the scenarios except for the one mentioned below where it performs a step which is not really required.

Let me explain the scenario in detail:

• Sheet 1 contains the list of trades as per client’s system and Sheet 2 contains the trades as per internal system
• Need to do several permutation and combination in Sheet1 in order to extract the unique ID’s from Sheet 2
• Scenario 1 – lets assume I have 100 records I concatenate few common fields in both sheets and use the simple code mentioned below to fill the all rows with the lookup formula
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
First scenario itself might populate the unique id’s for all the 100 trades or might give partial result which will prompt for the next scenario. If the first scenario generates the Unique ID’s for 99 records I can filter on “#N/A” and update a different formula to fetch the unique id using different combination. However in the initial stage I wouldn’t know the # of unmatched records because of which I have the standard selection till end and fill down codes in the macro, which would end up filling all the blank cells till the end, which takes unnecessary time.

Can anyone suggest a simple validation code which ensures that if the # of unmatched record is more than 1 only then the selection till end and fill down codes comes into picture otherwise simply update the formula and populate the result.

Apologies for the long question. Looking forward for your assistance.

Guys,

I found 2 ways to ensure that my formulas do not run into empty rows.

1. Define the range using the Offset+Counta formula to provide a dymanic range for the spreadsheet.

2. Use the subtotal and if functionality to ensure that where the count is <1 it doesn't repeat the then statement.

Regards,
Raghu