This function accepts both ranges and arrays, both horizontal and vertical Function TEXTJOIN(delim As String, skipblank As Boolean, arr) Dim d As Long Dim c As Long Dim arr2() Dim t As Long, y As Long t = -1 y = -1 If TypeName(arr) = “Range” Then arr2 = arr.Value Else arr2 = arr End … Read more
This is what I use to track the top 5 absentees… Edit to suit your needs. Formula in cell A1: =INDEX(A$13:A52,AGGREGATE(15,6,ROW($1:$40)/(B$13:B$52=B1),COUNTIF(B$1:B1,B1))) Formula in cell B1: LARGE(B$13:B$52,ROW())
You have two main errors in the code – not escapting the ” characters and using .Formula instead of .FormulaLocal. The ” characters should be written twice to show once in a string. See this code: Public Sub TestMe() Debug.Print “test””””” End Sub It prints test””. The last ” is for the end of the … Read more
Excel (Office 365) restarts automatically while executing UDF
You could use the VBA equivalent of a standard COUNTIF formula (which can take more than one argument). This can be expanded as required Standard Formula =SUM(COUNTIF(A:A, {“SAL”,”PRE”})) VBA Equivalent MsgBox Evaluate(“Sum(COUNTIF(A:A,{“”PRE””,””SAL””}))”)
You can do this with a formula: =IFERROR(INDEX($A:$A,IF(INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2>COUNTA(A:A),-1,INT((ROW(1:1)-1)/(((COUNTA(B:B)-1)*((COUNTA(C:C)-1)))))+2))&” “&INDEX(B:B,MOD(INT((ROW(1:1)-1)/(COUNTA(C:C)-1)),(COUNTA(B:B)-1))+2)&” “&INDEX(C:C,MOD((ROW(1:1)-1),(COUNTA(C:C)-1))+2),””)
Use INDIRECT() =SUM(INDIRECT(<start cell here> & “:” & <end cell here>))
.Formula accepts formulas in English. Parameter separator in English is ,. If you want to use formulas in the language of your Excel installation, use .FormulaLocal. However to ensure your code will run on any Excel, fix your formula to be in accordance with the en-us locale.
Instead of formulas, Why not use a PivotTable to crunch the numbers? You potentially face a longer one-time hit to load the data into the PivotCache, but after that, you should find a PivotTable recalculates much faster in response to filter changes than these computationally expensive formulas. Is there any reason you’re not using one? … Read more
The double-dash is known as a double unary operator. Try this link: Why use — in SUMPRODUCT formulae Specifically: SUMPRODUCT() ignores non-numeric entries. A comparison returns a boolean (TRUE/FALSE) value, which is non-numeric. XL automatically coerces boolean values to numeric values (1/0, respectively) in arithmetic operations (e.g., TRUE + 0 = 1). The most efficient … Read more