udf array formula

  1. M

    Need to get Sum of a Column based on values satisfying criteria in 6 different columns

    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...
  2. D

    VBA user defined function result to spill in two columns

    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...
  3. drpdrpdrp

    VBA for Dates Computation is too Slow

    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)...
  4. drpdrpdrp

    Loop through Range/Array - Vectorized Calculations (VBA UDF)

    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...
  5. M

    VBA for count sumif result for range (Macro or UDF)

    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...
  6. L

    Need assistance with VBA (array creation) for logarithmic regression forecast UDF

    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...
  7. R

    Replicate SUMPRODUCT in VBA to create UDF which is much shorter to simplify appearance of formulae

    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...
  8. R

    UDF to lookup and return multiple values concatenated into one cell without duplicates

    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...
  9. S

    UDF not working as expected

    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...
  10. S

    UDF VBA Array Part output

    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...
  11. S

    User Defined "Union" function to ignore "Nothing" entries

    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...
  12. X

    UDF and range arrays as input

    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)...
  13. M

    Parsing Text Fields

    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...
  14. R

    Modify UDF to receive and return array

    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...
  15. A

    Help: rewrite VBA function to accept array as argument

    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...
  16. C

    Udf returns Error when used in array formula

    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>...
  17. F

    Determining which values are returned from user defined function

    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...
  18. Gingertrees

    Function to search a text string for any of several smaller strings

    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...
  19. A

    User Defined Function with Array argument

    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...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top