Create array and formula based on 2 different sheets

Hi everybody,

A bit of a (from my perspective) complex query today, hopefully your fine selves will be able to make short work of this.

I have 2 sheets which are populated from another program using an OLE link. That's all fine, no problems exist.

The problem is, when I create the first sheet, the program will extract all data from my query, and the second will be a subset of that data.

I have 5 columns of data, so I need the 6th column to be a formula telling me if it is a duplicate cell.

I can do a Vlookup (=VLOOKUP(A2,'Results (Urgent)'!$A$2:$F$15,1,FALSE)) which tells me if the data exists in my second sheet, but I have to do it by hand. (E.g. Once the data is in, I know how big my dataset is.)

What I would like to do, is once my second sheet has been populated, create the Vlookup based on A) the size of my dataset in Results (Urgent), and B) copy the formula into each cell in my Results.

The only way I can think of doing this would be to create a For...Next, based on the size of results (For x = 1 to "Last Cell in Results") and then create a Vlookup based on the size of Results (Urgent) (ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'Results (Urgent)'!RC[-6]:R[ Last Cell in Results(Urgent) ]C[-1],1,FALSE)")

Sorry for the long ramble, I'm just trying to get every bit of data into this post so as to avoid confusion.

The question at the end of the day: How can I programatically insert a check which tells me if data exists in a sheet, based on varying datasets in 2 sheets?

Thank you for your assistance,

Bob

Bob

Couldn't tell what your second sheet was called, so assumed it was called results.

Please Login or Register  to view this content.

rylo

Hi rylo,

Thanks for that, worked perfectly. You were correct, my sheet was called Results, apologies for not specifying this.

As it happens, I had put together a workaround using 2 While statements to count each cell 1 by 1, which took twice as much code and would take a lot longer with more values.

Thank you again, this was a good excercise for teaching me how to write Range formulas.

-Bob