1. 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...
  2. J

    Creating a UDF to execute a formula

    I am trying to create a UDF that executes the following formula but I am unsure how exactly to structure the code for UDFs, more up to speed on run-of-the-way VBA macros. Any guidance would be appreciated...
  3. JenniferMurphy

    Best way to skip 1st & last cell in range in UDF?

    I have a little UDF that does some tallies on a column of cells (B2:B5). <tbody> R/C A B 1 Group Rating 2 North 25.2 3 South 14.7 4 East 52.0 5 West 33.8 6 Average 31.4 </tbody> I call the UDF from another cell like this: =Tally(B2:B5) I want to change that to include the cell...
  4. V

    UDF involving Split function or Formula Text?

    I created a UDF that calculates how many minutes it takes to complete a certain task. (ex: =MinutesToComplete(1,0.18,10,50,20) ) Is there a way to extract each argument/parameter? (ex: =RightParameter() ) and the answer would be 20
  5. V

    Udf vlookup

    I have a table of items that are assigned a fixed cost. Example: Item) Name) Price 1) Red Ruby Shirt) $35.22 2) Blue Tuxedo) $47.90 On a separate sheet I have a UserForm with a list box that grabs the table from the other sheet. I would like...
  6. JenniferMurphy

    UDF stops running if I hot-key to another task

    I have a UDF that does some iterative calculations. It can take anywhere from about 2 to 8-10 seconds to complete its work. The data, which I enter into an InputBox, comes from another application. If I hot-key over to the other application, the UDF stops running until I hot-key back. Is there...
  7. V

    adding error handling to UDF

    How can i better write this code and add error handling? Function TotalStainless(EasyorHard As Double, Thickness As Double, NumOfPierces As Double, HoleCutLength As Double, TotalPerimeter As Double) Dim PierceSecs As Double Dim HolesInchPerMin As Double Dim PerimInchPerMin As Double...
  8. M

    Make UDF based on multiple Match-criteria

    Has this formula made of Index and Match: =INDEX(Range,Match(1, (C:C=criteria1)*(D:D=criteria2)*(B:B=criteria3),0),5) The '5' represents the column number in Range that holds the cell with the value that should return as result of the formula. Basically the data table has 5 columns; #1 not...
  9. V

    How can i replace this "a" without application ran code?

    How can i replace the bold part? Function test1234() Dim a As Long If ActiveSheet.Name = "Sheet1" Then Else End If a = Application.Caller.Row + 1 Do While Cells(a, 1).Value <> "Yes" If Cells(a, 2) = "Yes" Then Exit Do test1234 = test1234 + Val(Cells(a, 2).Value)...
  10. V

    What does this mean on Microsoft’s website?

    Can someone please give me a detailed example of what this means? Do not use forward referencing and backward referencing To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells. Forward referencing usually...
  11. V

    UDF Loop Freezes Unless Calculating in The Sheet it’s in

    I have multiple UDFSs that loop through cells, I have application.volatile on them with worksheet change events that calculate the sheet. But if I am in a different sheet and press calculate, it freezes. Any suggestions?
  12. 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...
  13. O

    UDF Error with automatic calculations

    The formula below is being used in a table to see which cells in the range have a particular conditional formatting color. When I enter the formula in the first row and excel auto-fills the other rows, the formula works. If I turn on automatic calculations, it gives #Value !. If manual formulas...
  14. D

    Autofit Row Height not working on a cell containing a formula

    Hey folks, I've got a spreadsheet which uses formulas to keep the data in a single ("master") location, with the "alternate views" simply displaying the contents of that master cell. However, I've discovered that in some cases the Autofit function for rows (autofit row height) doesn't correctly...
  15. V

    Can anyone think of a better way to write this VBA or to turn it into a UDF?

    Sub test4opsgroup() Application.ScreenUpdating = False ActiveSheet.unprotect Dim cl As Object Dim l As Long, strCells As String Dim strpart As String Dim min As Range Dim numrows As Long Dim numcolumns As Long Dim partnum As Range Dim Qty As Range ActiveSheet.Range("T2").Select For Each...
  16. V

    How can I change this to a certain range rather than entire column?

    How can I refer to a range rather than entire column? Thanks Function Level5MakeMaterialCost() Dim R As Long Application.Volatile (True) R = Application.Caller.Row + 1 Do While Cells(R, "BN").Value <> "Yes" If Cells(R, "BM") = "" Then Exit Do Level5MakeMaterialCost =...
  17. V

    VBA is Adding Microsoft Excel Objects??

    Everytime I am opening my workbook, I am getting a catastrophic failure, and then the Microsoft Excel Objects duplicate on each tab module. What am I doing wrong?
  18. V

    UDF For Each to end after certain range

    I was thankful enough to receive a UDF from Rick Rothstein. https://www.mrexcel.com/forum/excel-questions/1101059-udf-sum-until-certain-column-not-blank-again-post5294214.html#post5294214 I was wondering if someone can help me and figure out a Do Until a certain range, I have to uses over 30 of...
  19. C

    UDF returning an array and array formula

    I have written a UDF which returns an array and want to display all of those values using an array formula on my sheet. The UDF is: Function GetDutyDetails(Rng As Range) As Variant Dim ArraySize As Integer ArraySize = Worksheets("ATCO").Range("atco_non_operational_duties").Rows.count Dim...
  20. A

    Need Custom Function to Format Cell Values

    I have values on a source sheet and a working sheet. On the working sheet, I pull values from matching cells (Working!B6 gets the value from Source!B6 etc.) I need a custom function which looks at a cell such as Working!B1 to decide what to do in Working!B6: If it is V or D, then add an...

Some videos you may like

This Week's Hot Topics