Placing UNIQUE formula in a cell; FORMULA, FORUMULAR1C1,FORMULA2R1C1

[jomili]

I'm working on a macro, and having a little trouble with my formula. In a nutshell, I have a long list of Budget Analysts in a Column named "BA" on one sheet. On the other I want to place this formula to get a unique list of those BAs, minus any "NA's" in the column. My formula is this:

Please Login or Register  to view this content.

Below is my in-progress macro. My issue is that if I use the VB editor to record my formula, it records as "Formula2R1C1", but then when I revamp it for my macro I can't put it in using either of the R1C1 formula strings, but instead have to do it as "Formula =", as shown in my macro below. The problem with that is it puts the @ symbol in front of my formula, which I then have to remove.

Please Login or Register  to view this content.

I'm sure it's a simple problem I'm overlooking, so I'd appreciate a good second pair of eyes to look at my code. I've attached a workbook so you can see what I'm working with. BTW, all of my invoices will have a BA column, but it moves around in the sheet, so I have to use FIND to be flexible.

[rorya]

Shouldn't you be using Formula2 not Formula?

[jomili]

well, by the evidence of "it works", I'd say yes, but that leads me to "what's the diff between between Formula and Formula2, and why don't the R1C1's work for this?"

[rorya]

It's a dynamic (spilling) formula, not a regular one, so you need Formula2. Your formula string isn't in R1C1 format so I don't know why you want to use the R1C1 properties.

[jomili]

So then Formula2 is for the new spill formulas? I didn't know that.

This is the first time I've seen Formula2, AND the first time I had a formula that didn't use the R1C1 format, so I got hit by two surprises at once. Thanks for bringing clarity.