Asked  7 Months ago    Answers:  5   Viewed   97 times

I am adding a formula to a worksheet via VBA which should be:

=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""))

This utilises the new SPILL feature in Excel to give me a list of column B values where the related value in column A matches what is in cell A. I'm also applying the UNIQUE function to remove any multiple blank ("") results.

This works perfectly if I manually type the formula into Excel, however in using VBA to add the formula, Excel is adding @ symbols within the formula, and causing it to show #VALUE!.

The VBA line being used to add the formula is:

=Cells(x,y).Formula = "=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""""))"

The resulting output in Excel is:

=@UNIQUE(IF(TableA[@[ColumnA]]=A1,TableA[ColumnB],""))

What is going on, and what have I missed?

Thanks in advance!

 Answers

46

Good question, and I looked it up...


In short:

Use =Cells(x,y).Formula2 instead of =Cells(x,y).Formula


Explaination:

The @ that shows is called the implicit intersection operator. From MS docs:

Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background).

But why does it appear in your newer Excel O365? Well, Range.Formula uses IIE (implicit intersection) thus adding the @ to basically undo your dynamic array functionality. UNIQUE is a new dynamic array function. So, to write this out in code, you should use the Range.Formula2 property (or Range.Formula2R1C1 if you use R1C1 notation). These properties use AE (array evaluation) and is now the default.

  • Here is an informative doc from MS on the subject which explains the difference between Formula and Formula2 in more detail.

  • If you want to know more about the implicit intersection operator then have a look at this

  • I answered another question earlier on that involved implicit intersection with an example on how that actually works here if one finds it interesting.

Tuesday, June 1, 2021
 
insomiac
answered 7 Months ago
64

This isn't exactly intuitive, but you cannot Redim(VB6 Ref) an array if you dimmed it with dimensions. Exact quote from linked page is:

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).

In other words, instead of dim invoices(10,0)

You should use

Dim invoices()
Redim invoices(10,0)

Then when you ReDim, you'll need to use Redim Preserve (10,row)

Warning: When Redimensioning multi-dimensional arrays, if you want to preserve your values, you can only increase the last dimension. I.E. Redim Preserve (11,row) or even (11,0) would fail.

Friday, June 18, 2021
 
tompave
answered 6 Months ago
80

You should always aim at converting something that looks like a date into a date. A date will always remain a date and you can display it in whatever format you want. It will even survive the transition from one language to another.

So if your source is a date you are fine. If it is a string, I would do this:

  1. convert the source string using =DATEVALUE() ... this will work if the month name is in the system language
  2. if temporarily changing your system language is not an option, the next would be =DATE(yy,mm,dd) whereby the arguments must be created using =LEFT(...), =MID(...), =RIGHT(...)
  3. eventually you must convert month names from one language to another like in the following example:

    A1 contains JANFEBMAR...

    A2 contains JÄNFEBMÄR...

    conversion formula =MID(A2;FIND("MAR";A1);3)

Edit in reply to Kyle's comment:

Besides the fact that the German(Austria) locale would immediately recognize this string as a date, let's split the problem into chewable pieces. Your date string is asumed in A1

  1. the time part is easy: it is an additive term TIMEVALUE(RIGHT(A1;8))
  2. splitting the date we have to fight against seperators at dynamic locations. The positions of the delimiters can be found by

    2a) =FIND("/";A1;1) ... find position of first delimiter

    2b) =FIND("/";A1;4) ... 2nd delimiter, asuming the first figure can be only 1 or 2 char's and no blanks before - alternatively we must replace the constant "4" by term (2a)+1

    2c) =FIND("/";A1;FIND("/";A1;1)+1) ... safer version of (2b)

  3. now we have most things ready to construct our left's mid's and right's

    3a) =LEFT(A1;FIND("/";A1;1)-1) ... 1st figure ... length of (2a)-1

    3b) =MID(A1;FIND("/";A1;1)+1;FIND("/";A1;FIND("/";A1;1)+1)-FIND("/";A1;1)-1) ... 2nd figure ... start:=(2a)+1, num_Chars=(2c)-(2a)-1

    3c) =MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4) ... 3rd figure ... start:=(2c)+1, num_chars = 4 - asuming the year is always 4 digit. the more abstract case would be a (3b) with (2c) being a =FIND() for the first blank

How to interpret especially the 1st and 2nd figure (i.e. MM/DD or DD/MM) is up to you.

  1. now comes the fun part, i.e. concatenating all these formulae into one monster to get the date

    4a) start by entering =DATE(1;2;3)

    4b) replace 1 by (3c), 2 by (3b), 3 by (3a) ... do not copy the leading "="

    4c) =DATE(MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4);MID(A1;FIND("/";A1;1)+1;FIND("/";A1;FIND("/";A1;1)+1)-FIND("/";A1;1)-1);LEFT(A1;FIND("/";A1;1)-1))+TIMEVALUE(RIGHT(A1;8))

You don't see the time, only the date! .... Remember to give a custom cell format displaying date AND time, i.e. "DD.MM.YYYY hh:mm:ss"

OK ... this formula is absolutely unreadable and un-understandable, so you might want to display intermediate results in (temporary) fields/columns.

And this formula will work only if the input string is more or less strictly formated. It can cope with some added blanks at the first and second numbers, but starting with YYYY it becomes tricky. Then other concepts need to be included, like removing all blanks by =SUBSTITUTE(A1;" ";"") before all others, etc. etc.

Hint: I am always building complex formulae like this: isolate the terms in individual cells and later merge them to one large formula

Friday, August 6, 2021
 
SheppardDigital
answered 4 Months ago
93

Short solution description:

You could do the whole thing with a couple of arrays and a dictionary. Use the dictionary to group by element, and then have an array for the associated value. The array would have 1D as concatenation of values encountered so far for that element (with a delimiter to later split on), 2D as being the cumulative total.

Note:

  1. This approach does NOT assume your input is ordered - so can handle unordered input.
  2. The advantage of using arrays is the speed. It is much faster to work with arrays than to incur the overhead of repeatedly touching the sheet in a loop.

Library reference needed:

Requires a reference to Microsoft Scripting Runtime via VBE > Tools > References. See link that explains how at end.


VBA:

Option Explicit

Public Sub ApplySubTotals()
    Dim lastRow As Long
   
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lastRow < 4 Then Exit Sub
      
        Dim arr(), dict As Scripting.Dictionary, i As Long
     
        arr = .Range("A4:B" & lastRow).Value
        Set dict = New Scripting.Dictionary
      
        For i = LBound(arr, 1) To UBound(arr, 1)
            If Not dict.Exists(arr(i, 1)) Then
                dict(arr(i, 1)) = Array(arr(i, 2), arr(i, 2))
            Else
                dict(arr(i, 1)) = Array(dict(arr(i, 1))(0) & ";" & arr(i, 2), dict(arr(i, 1))(1) + arr(i, 2))
            End If
        Next
 
        ReDim arr(1 To lastRow + dict.Count - 3, 1 To 2)
        Dim key As Variant, r As Long, arr2() As String
      
        For Each key In dict.Keys
            arr2 = Split(dict(key)(0), ";")
            For i = LBound(arr2) To UBound(arr2)
                r = r + 1
                arr(r, 1) = key
                arr(r, 2) = arr2(i)
            Next
            r = r + 1
            arr(r, 1) = "Subtotal": arr(r, 2) = dict(key)(1)
        Next
        .Cells(4, 4).Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    End With
End Sub

Side note:

It may be possible that it is more efficient to update items within the array associated with each key as follows:

If Not dict.Exists(arr(i, 1)) Then
    dict(arr(i, 1)) = Array(arr(i, 2), arr(i, 2))
Else
    dict(arr(i, 1))(0) = dict(arr(i, 1))(0) & ";" & arr(i, 2)
    dict(arr(i, 1))(1) = dict(arr(i, 1))(1) + arr(i, 2)
End If

I will need to test when I have more time.


Want to know more?

As a beginner, here are some useful links:

  1. https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object
  2. https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/using-arrays
  3. https://docs.microsoft.com/en-us/office/vba/language/how-to/check-or-add-an-object-library-reference

Monday, November 8, 2021
 
panthro
answered 3 Weeks ago
97

Not sure how set you are on VBA, but depending on your version of Excel you can do this with dynamic arrays, for example:

enter image description here

Formula in B2:

=--ISNUMBER(FIND(""""&B1:K1&"""",A2))

EDIT:

As per @T.M. his valuable comment you can feed the 2nd parameter in FIND() a whole vertical range too:

enter image description here

Formula in B2:

=--ISNUMBER(FIND(""&B1:K1&"",A2:A4))
Monday, November 8, 2021
 
Thibel
answered 3 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share