Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Need fix re Issue using filter function in VBA

0

I build a string using the filter function to assign to cell.formulaR1C1 but excel inserts an @ sign after the = and before FILTER, (=@Filter) This prevents to spill.

I can leave VBA code and manually edit out the @sign but that defeates the purpose of the VBA code.

Under certain conditions I achieve the spill, under others I do not.

Objective:   Recategorize groups based on new group assignments

Overview:   The filter function allows me to redistribute items under new groups

       Issue:    Excel inserts an @ sign before FILTER in certain conditions, preventing                      desired array 

Step 1:  Write the group names into an array using filter

  While the WorksheetFunction.Filter does not work, I can write a formula to a  cell and read the resulting array into an array

  Issue: The cell formula to produce this array is =FILTER(A2:A121,A2:A121<>"","")

            Assigning that string cell.formula results in              =@FILTER(A2:A121,A2:A121<>"","")  and no array

            Assigning the equivalent string in R1C1 reference to cell.formulaR1C1 work    

Step 2:  Loop through the category array filtering the items based on the group assignments

  Issue:  Excel inserts and @ sign before FILTER in both cell.formula and cell.formulaR1C1

Stepping though the code in the attached workbook will demonstrate how the first use of the filter function is successful but how the next attempt results in Excel placing the @ sign into the formulaR1C1 string.

excel
MS Office Version: Microsoft 365
posted by: r_guild (rep: 6) on Mon Apr 19, 2021 at 1:54 pm
Answer
Discuss

Discussion

What is your question?
Variatus (rep: 4889) Apr 19, '21 at 6:47 pm
My question is why is Excel inserting an @ sign and what can I do to prevent that
r_guild (rep: 6) Apr 19, '21 at 6:59 pm
To give a complete answer, when the @ sign is in the formula, I get the first matching item only, not all matches.
r_guild (rep: 6) Apr 19, '21 at 7:18 pm
Add to Discussion

Answers

0
Selected Answer

Excel inserts the @ sign to prevent the spill. I haven't found a way to prevent it. I also haven't found a way to manage the result.

Basically, there is no way to know how big the spill might be. Therefore a simple way to provide space for it would be to have one formula per column so that the result can spill in the cells below the formula. But for that kind of result you would be better off using VBA's Filter function.

This argument also holds if you are thinking of placing new formulas at the end of the spill dynamically.

Therefore I think you should review your target. Right now it seems that your target is to use VBA to insert a formula that will do the job. It's a bit like telling the mechanic to call a mechanic to fix your car. If you would focus on the job you would find better ways of doing it. Why can't VBA do the job? Or, for that matter, why can't you just enter the formulas in your workbook manually and forget about VBA?

posted by: Variatus (rep: 4889) on Mon Apr 19, 2021 at 7:29 pm
Discuss

Discussion

Oops, I wasn't aware of the VBA filter function until you brought it up.  I immediately did a Google search an found it on Excel functions.net. Do you provide your own source for me to build my own mental reference library?

I know you are doing this to build a business and I'm not a floor scratcher.  I watch almost all of your video tutorials, along with several others.  I'm retired but wrote some pretty comprehensive VBA apps in Excel and Access during a 15 year period ending a decade ago.

I have been a developer in other languages and platforms, one installed in over 40 hospitals, another, an accounting system for the oil & gas industry.

I love this stuff, like Scotty got excited when Captain Kirk confined him to quarters because it gave him the opportunity to learn about all the great new stuff.  My first computer password to begin coding on a mini-computer in 1980 was "Master" :) 

Stay tuned.  I'm a fan and ready to be a client when gig work revenues begin.

However, I'm retired, looking for gig work for supplemental income and intend to pay for additional training once I can do so from that income.
r_guild (rep: 6) Apr 19, '21 at 8:46 pm
Thanks for the feedback. I'm retired, too, and so is John, I suspect. Don is the one with the brains around here and he gets the money. But if you got time and wisdom to spare do join us in answering questions. It's good fun and I, for my part, learn a lot.
Variatus (rep: 4889) Apr 19, '21 at 9:48 pm
Variatus- good guess! Technically I'm not retired since I'm not yet drawing any pensions but I quit work then Coronavirus came along...

I agree with you that helping on the Forum is good fun and adds some mental challenges. What's more I get to learn from the Answers from you and Don and from  r_guild in future hopefully!) 
John_Ru (rep: 6142) Apr 20, '21 at 1:45 am
BTW Variatus, you say Don is "... the one with the brains around here..." but seems to me you have plenty too. 
John_Ru (rep: 6142) Apr 20, '21 at 3:20 am
I did find a VBA solution that elininates the need for using the Excel filter function but the VBA filter function can neither output multidimensional arrays nor can it filter out blank array elements.

I, instead, used REDIM to build the category array, loaded the entire dataset into a multidimensional array(dataset), and created an output array:

ReDim output(UBound(dataset, 1), 4)
and finally writing to the sheet with  Range("A2").Resize(UBound(output, 1), UBound(output, 2)) = output

The speed was good since I kept it all to arrays.  More code but only VBA code.

A few side notes
Arr=application.worksheetfunction.unique(rg) - works, one line of code,
The problem is that blank cells create a 0 item in the array.

Documentation says spill is, in effect, an array but ubound won't work.
However, you can establish the size of the spill using currentregion.rows.count.

Also, a multicell selection is an array of values which can be accessed by selection(#,#) but ubound doesn't work.

Bear in mind, I was looking for solutions, had a hammer that might work, and was trying it out.  I ran into issues wanted to see if there was an elegant solution.

This is about building my own solutions toolbox.
r_guild (rep: 6) Apr 20, '21 at 8:47 am
Hey, guys, are you interested in seeing code to create a floating toolbar that always appears by the cell you selected? No form, just using adjacent cells.
r_guild (rep: 6) Apr 20, '21 at 9:04 am
Floating toolbar sounds fun but why don't you suggest it to Don for consideration as a new Tutorial? 
John_Ru (rep: 6142) Apr 20, '21 at 9:11 am
How do I contact Don?
r_guild (rep: 6) Apr 20, '21 at 1:17 pm
Don may answer but, if he doesn't by tomorrow, you could use the Submit A Tip link in the Forum header (I never have but pretty sure he vets any suggestions). 
John_Ru (rep: 6142) Apr 20, '21 at 2:05 pm
You are correct John - Don does review the tips and vets them. I submitted one on how to create a Table of Contest sheet in a workbook - very helpful for workbooks with a large nymber of sheets. Don liked it and made it into a tutorial. (my 15 minutes of fame  ;-)  ) I have learned a lot from reading what you, Variatus, and Don write. For myself, this forum and the entire site is the most helpful. I am still working so I don't have as much spare time as you retired folk, but I do enjoy being able to offer help when I can. 
WillieD24 (rep: 547) Apr 20, '21 at 6:39 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login