I'll save you a long-winded explanation and keep this short and sweet. Basically I want to use VBA to create a list, which is a list made up of common values between two other lists (zip codes). Essentially I want to do an INNER JOIN for those folks familiar with SQL. I can do it easily by typing in the formula in the first cell directly, using =FILTER(list1,COUNTIF(list2,list1)) to accomplish this join: =UNIQUE(FILTER(inputs!R8C7:R3610C7,COUNTIF(CleanVGRevenue!R9C2:R1027C2,inputs!R8C7:R3610C7))) and this method automatically fills out the entire joined list/table to the proper size, which is what I want. Great. So I went to put this into VBA, because I want an automated solution, replacing the hard coded values with variables that adjust to the number of zips in each list (n & nVGR):
Sub JoinCommon()
Dim sInput: Set sInput = Workbooks("VGadjustment.xlsm").Sheets("inputs")
Dim n: Let n = sInput.Cells(5, 8).Value
Dim cVGR: Set cVGR = Workbooks("VGadjustment.xlsm").Sheets("CleanVGRevenue")
Dim nVGR: Let nVGR = cVGR.Cells(1, 3).Value
cVGR.Cells(9, 5).FormulaR1C1 = "=UNIQUE(FILTER(inputs!R8C7:R" & n + 7 & "C7,COUNTIF(CleanVGRevenue!R9C2:R" & nVGR + 8 & "C2,inputs!R8C7:R" & n + 7 & "C7)))"
End Sub
This almost works, however when the macro runs, the formula being entered to the cell is actually replaced with =@UNIQUE(FILTER(inputs!R8C7:R3610C7,COUNTIF(CleanVGRevenue!R9C2:R1027C2,inputs!R8C7:R3610C7))) Note the @ symbol. Since excel puts this in, it only returns the first line and does not fill down automatically like I want. I get what the symbol does, but I don't want it there. How do I stop excel from putting it in automatically when using VBA? Why is put in automatically?
Thank you all for any help. It's greatly appreciated.
EDIT: Here (https://i.stack.imgur.com/av5nV.jpg) is an image of what I have currently, and here (https://i.stack.imgur.com/vV98y.jpg) is an image of what I want to have happen. I would imbed the pics if I could but I guess I need 10 reputation to be able to post images.
