AutoFill Destination Unknown Range

Hopefully I can articulate this....I have a Workbook which I cannot post a lot of the code to because it is proprietary but basically I have prompted the user to load 3 files that data will be retrieved and used from. I have assigned Name Ranges to "Grids" "Elements" and "TEMP" in this code. The workbook has found, copied, and pasted data from one sheet to another. (I'll post specific code below this for clarification) In this new sheet I have moved over one cell to the right to start a new column next to it since the data is still selected from the paste. This new column was given a title and I know need to apply a formula to the first cell and copy it down the length of original data (so I have 2 equal columns).

Please Login or Register  to view this content.

Essentially I'm stuck on the Range since I won't know in advance how long the column is. Is there a way basically tell the code to copy the formula down until there is a blank cell and to just stop?

Thank you!

Yeah, I noticed the error right after I posted this question. So the formula as it would be written is how you have it shown (without the application.worksheet, etc) and if I write it that way I still get the same error. Maybe I'm not understanding?

The formula isn't in a cell, I'm trying to create it inside of VBA and apply it to a cell. Grids, Elements and TEMP are named ranges (via the ActiveWorkbook.Names.Add Name:=). The formula is what it needs to be as if I were going to put it just inside a cell and not use VBA.

Sorry, the formula is what is posted. Yes, I tried the code and it worked, but is calling C1 instead of B3 and working it's way down. In otherwords the formula needs to be applied to C3 but the Match needs to reference B3, then C4 and B4, etc... I tried changing the R1C to R3B but it apparently doesn't work that way, what is R1C exactly?

After further investigation I now understand R1C1....if I change the R1C in the formula to R[0]C[-1] it will select the correct cell in the previous column. Thank you!

Good to know, thank you!