relative cell address in vba array formulas

I'm trying to program my array formula into a column of cells using vba, but I'm finding where I want a relative row address I'm getting an absolute address, always 6. Can anyone help me with this please?

I currently have

Please Login or Register  to view this content.

Using R1C1 notation, square brackets denotes relative references and omitting brackets constitutes an absolute reference.

So, for ex:

In your formula RC[-1] would be a relative reference regarding the column being 1 to the left of the current cell in which the formula is placed, ie: if the formula was in C2, this reference would be B2.

On the other hand RC-1 likely isnt even a valid entry since there are no negative columns, as this form is saying current row the formula is in but absolutely reference the -1 column (column to the left of column A, which isnt a thing)

https://bettersolutions.com/excel/fo...1-notation.htm

If you apply FormulaArray to a multiple cell range, it’s the equivalent of selecting all those cells, typing the formula and then pressing Ctrl+ Shift+Enter. You either need to use FormulaArray on the first cell only, and then use Filldown or Autofill to copy the formula down, or you need to refer to the entire range (“R6C4:R” & lastrow & “C4”) rather than just one row with RC4.

It might be helpful to do a macro recording whilst manually putting in exactly what formula, or formulas that you want, then take a look at what the macro recorder gives you for formulas ( and everything else ) in the recorded code - VBA does "R1 C1" stuff



Edit: If you do that , remember that
R
is the same as
R[0]
but personally I would replace any R from the recorded code with R[0] - just a personal preferance to remind me that it is a relative reference

Originally Posted by jlt199

Any ideas as to why my solution is SO SLOW and how I might speed it up?

Depends on what you consider slow (how long does it take you to run it) vs what your macro is doing. The sample you gave is a very small data set, is your actual file a much larger dataset? If your actual file is larger, how much data are we talking about and is your code any different than in your sample?

EDIT: a glance at your code and I notice a few things.

• You have declared variables you are not using (serial, tooltype are unused variants)
• Your method for finding last row and last column is likely a pretty slow method for doing so (via Find method using a wildcard as it evaluates each cell)
• If the sheet on which the named ranges are is static it is likely better off creating the named ranges using a dynamic named range so it self adjusts instead of doing it via a macro

You may consider adding screenupdating off/on to your code to speed it up as well.

Also, I am unclear on why you need the formula in all those cells as it always returns the same answer in all of them. The formula itself could likely be more efficiently written however I am not entirely sure what its intended goal is.

Otherwise I am not seeing anything that stands out as a chance to speed up your code. If I had to guess your last row/column lines are likely the biggest room for improvement as find can be slow on large data sets, especially with wildcards and especially since there are many alternative ways of determining last row/col

So it sounds like its likely the array formula adding most of the time in the form of calc time.

You can in your code toggle to manual calculations and at the end toggle back to automatic. This should at least speed up the macro run time.

As for after the macro, its most likely the array formula at fault. How much data are we talking about filtering roughly (10 columns x 1000 rows, 10000 rows, etc)? Whats the size of your file in KB or MB? Does filtering with manual calculation turned on take a long time as well (Important to know this!)?

I am not sure I agree about the formula needing to be an array formula, ill see if I can work out another formula. Also, why do you need the formula in more than 1 cell on the sheet if each instance of the formula returns the exact same result? What is your formula meant to do (Im not confident it does what you want)?

Originally Posted by jlt199

What I intended was to find the most frequently used tooltype for each serial number

Ah ok that explains filling it down the list. So the expected result is its either "tethered" or "ILI" on each line, according to which has the most occurrences of the serial number from the current row?

See attached sample using regular non-array formulas. Presuming I understood your intended results it should be much faster and more accurate (yours was totally not working, each one in your sample returned the IFERROR error value).

I left your formulas in for comparison.