udf array formula

I have a requirement to evaluate a sum of a single column, if 5 other columns satisfy individual criteria. Each of this columns would have array of values. Considering following subset of data DIFFERENCE_PERIOD_NET PERIOD_NET_DR PERIOD_NET_CR PERIOD_NUM SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT4...

Hi all, I am trying to create a user defined function using VBA. The goal is to create a formula based pivot out of two columns - one containing names and the other containing amounts. The formula should throw out unique list of names in first column and sum of those names in second column I...

The following VBA UDF returns a 4-value-array using a Date value as input. Even though it is simple, if I ran for ~10'000 rows calculation time is not satisfactory (>30seconds). What am I doing wrong and how should I write the code to make it faster? ' YrMoDoy Function YrMoDoyDom(InputDate)...

I understand that I can take a range of values and make a vectorized calculation on it by using {...} in plain excel (Method 2) (Method 1) below is showing the simplest way of accomplishing what I need: 1. Take the values in a range/vector/array, 2. subtract a fixed value from each 3. save as...

Hi Guys. I want to write UDF or macro to count sumif result if more than one criteria. I write both But have Problems. 1. User Defined Function: Function CountSumResult(InputRange As Range, Criteria As Variant, SumRange As Range, SumCriteria As Long) As Long Dim Cell As Range Dim...

MY GOAL: My goal is to create a uniform set of UDFs that perform forecasts under various forms of regression, such as exponential, logarithmic, and power. Excel has several native functions for forecasting under various forms of regression, but there are several and the syntax is not uniform. I...

Hello, I would like to know how to create a UDF in VBA that replicates a SUMPRODUCT formula. Is this possible and how can I do it? I am new to VBA so not familiar enough with the code to create this myself. For example - UDF - HO() = SUMPRODUCT((Item Index range=X)*(Date Index...

I am looking at a few thousand rows and need to locate the matches, remove duplicates for each, and concatenate the remaining values into a single cell with a delimiter. Example: The server list column should be compared against another sheet that contains a list of server names and owners. If...

I have a UDF I based off of a prior I was using. The prior one works just fine. But for some reason, this new one just returns a #VALUE error. I have 3 columns with Headers of: TD ST FLR In the FLR I input my UDF and it is based off of the TD & ST. Could someone give it a look over and...

Hello all, I've the below user defined function which works well, however i would like to extract only part of the output using vba, in the below case it is the second output glon2. I know how to do this in a spreadsheet as a CSE function however i don't intend on putting the results into a...

Hello, I have a user defined function as seen below that allows me to combine two non-adjacent columns into a contiguous array. Function MakeContig(ParamArray av() As Variant) As Variant Dim avOut() As Variant Dim i As Long Dim j As Long ReDim avOut(1 To...

Hi guys. I'm new here and i'd like to see if anyone know how to make this work. In a Module i made this function: Function PProduct(arr() As Variant) As Double Dim soma As Double Dim i As Long soma = 1 For i = LBound(arr, 1) To UBound(arr, 1) soma = soma * arr(i, 1)...

I have a column containing mixed data that I need to parse and extract a particular portion from but I've run out of brain power in figuring this one out. The data in the column is a combination of string codes and number codes that are joined by a varying number of spaces (no apparent rhyme or...

Hello all, I have a worksheet that lists the value of various commodities in a single cell. e.g. in Cell A1 3 Piles, 5 Masts, 3 Beams There are many such cells on a worksheet and I would like to calculate totals for each commodity. To do this my thinking is to find the commodity start...

Hi guys, I'm trying to use a custom function that we're supposed to use at work, but the problem is it doesn't accept array as argument (so I can't do ctrl+shift+enter). This is a custom percentile function: Function LPercentile(rArray As Variant, p1, Optional AtPosition As Boolean) As...

I am new to the forum and to vba. It seems this shouldn't be to hard to fix but I'm having trouble.This is an example, but my actual book contains more than 4,600 entries; I use Excel 2010. <tbody> A B C D E F G H I J 1 Data IsNumber IsFormula 2 5 HH 7 3 4 8 T 5 #VALUE! </tbody>...

Hello, I have a function that returns an array, however I don't want/need all of the outputs all of the time. Is there a way for my inputs to determine which values are outputted? Example: Function car(person as String) Dim info(3) as variant state = 0: county = 1: make = 2: model = 3...

Summary: Inherited some data - now trying to categorize products based on a description in a text field. Details: Identifying info could be across several fields, so I concatenated the data into a single messy text field. Easiest analogy would be to categorize cars as being made by GM or Ford...

I am trying to create a User Defined Function with Array arguments that I want to use in an Array formula. After some debugging, I isolated the problem to this. VBA Code : Public Function MyFn(MyRng As Range) As Variant MyFn = 2 End Function In the worksheet, array formula...