VBA adding symbols, disabling the intended code. Please help.

[afrazier1221]

As a backdrop for my question/issue, I'm building a database-like spreadsheet with a user form for tracking my mobile automotive business, and because of the potential number of records over time, I'm trying to avoid any in-cell formulas as I can to prevent it from bogging down later due to massive numbers of calculations. Yes, I can use Access, but I like the look of Excel better, and I'm more familiar with the coding.

So, in the interest of minimizing formulas, what I'm doing is using VB to write the necessary formulas into the cells when needed. Once the formulas do their calculations, VB copies the results and overwrites the formulas with those results so the formulas won't remain and take up memory.

For some reason that I don't understand, and don't begin to fathom where to start looking to overcome it, VB is adding an @ in some of the formulas it writes, preventing spill.

For example, as part of a loop, I have the code:

Sheet5.Cells(E, 6).Value = Chr(61) & "FILTER(Vehicles!$A:$A, Vehicles!$B:$B = Calculations!$A$2)"

This, on Sheet5, Cell F2, should return:

=FILTER(Vehicles!$A:$A, Vehicles!$B:$B = Calculations!$A$2)

Instead, it returns:

=@FILTER(Vehicles!$A:$A, Vehicles!$B:$B = Calculations!$A$2)

Because of the @, the spill feature is disabled. As I said, it's part of a loop. If this formula is written into the first cell of a series in a column, the code spills over to successive rows for as many instances of the search criteria as it finds. If I put the formula in each cell independently, it gives me the first result in each cell, and never the successive ones that also match. If the @ is there, the spill feature doesn't work. If it writes it with the @ and I manually remove it from the formula, the spill immediately works and makes everything function as it should.

So, ideally, I'd like to have:

E = 2
F = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Sheet5.Cells(2, 6).Value = Chr(61) & "FILTER(Vehicles!$A:$A, Vehicles!$B:$B = Calculations!$A$2)"
For i = 0 To F
Sheet5.Cells(E, 7).Value = Chr(61) & "NV(VLOOKUP(F" & E & ",Vehicles!$A$2:$F" & F & ",3,FALSE)&" & Chr(34) & Chr(32) & Chr(34) & "&VLOOKUP(F" & E & ",Vehicles!$A$2:$F" & F & ",5,FALSE)&" & Chr(34) & Chr(32) & Chr(34) & "&VLOOKUP(F" & E & ",Vehicles!$A$2:$F" & F & ",6,FALSE))"
E = E + 1
Next i

(Ignore the NV. That's a custom function I made to return a blank cell if the result of a formula returns a blank, zero, or error).

What this is supposed to do is count the number of vehicle records from the vehicle page, write a FILTER command in the second row (first row of the table) of column F on the Calculations page, let the FILTER command spill to provide all the results of the filter, and then write corresponding VLOOKUP formulas in column G to look up the filtered numbers and return the vehicles belonging to the customer.



Instead, VB is putting the @ in the formula, the spill isn't happening, and I'm getting only a single result.



Any guidance on this would be extremely appreciated.

 

[afrazier1221]

Nevermind. Got it.

E = 2
F = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Sheet5.Cells(2, 6).Formula2 = Chr(61) & "FILTER(Vehicles!$A:$A, Vehicles!$B:$B = Calculations!$A$2)"
For i = 0 To F
Sheet5.Cells(E, 7).Formula2 = Chr(61) & "NV(VLOOKUP(F" & E & ",Vehicles!$A$2:$F" & F & ",3,FALSE)&" & Chr(34) & Chr(32) & Chr(34) & "&VLOOKUP(F" & E & ",Vehicles!$A$2:$F" & F & ",5,FALSE)&" & Chr(34) & Chr(32) & Chr(34) & "&VLOOKUP(F" & E & ",Vehicles!$A$2:$F" & F & ",6,FALSE))"
E = E + 1
Next i

Apparently this is an implicit intersection operator, and can be avoided with the .Formula2 extension. I tried it. It works. Hope my frustration is a help to someone else.