1. DRSteele

    Function SEQUENCE is producing errors.

    The new Excel function SEQUENCE seems to be producing errors. I want a sequence of values from -50% to 50% with a 5 point step. Put this in a cell and evaluate it in Edit mode by pressing f9. =SEQUENCE(21,,-0.5,0.05) Not only is the 0 a wonky exponent, some of the other values near zero are...
  2. M

    Passing an integer variable into VBA evaluate SumProduct

    I'm trying to pass a variable into my evaluate statement and I can't get the syntax correct. Let me know if you can help This works: Labor = Evaluate("=sumproduct(--(g10:g50000="Forecast"), --(j10:J50000="CWIP"), --(y10:y50000=2020), --(z10:z50000=4),(aq10:aq50000))") but I want to change the...
  3. C

    Trim, Clean using Evaluate running into 256 character limit

    Hello I have large datasets that I run automated processes on that I need to trim and clean and have been using: Function CleanSheets(arrShtNames As Variant, startRow As Long) Dim ws As Worksheet Dim rng As Range Dim LR As Long, Lc As Long For Each ws In Worksheets(arrShtNames) With ws...
  4. T

    Evaluate math formula from Equation editor into cell

    Hello MrExcel community, this is my first post, so if there is anything about this post that isn't posted correctly feel free to tell. OK so going into the main thing, In my Excel sheets I use plenty of long and complex mathematical equations, that when trying to replicate form paper into...
  5. T

    Evaluate formula

    Assume I have this data on my worksheet from cell A1 through to B6: <colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody> 1 a 2 b 3 c </tbody> If In cell D1 I type: =MATCH(1,(A1=A1:A3)*(B1=B1:B3),0) and enter it "normally", I...
  6. J

    speeding up loop

    Hi everyone I have a loop that is already working. I was just thinking if there is a way to select all the cells that is zero and deleting it at one go instead of sorting it using a loop 1 by 1 by vba. this have to be done in vba as this is just part of the code in a very long line of codes...
  7. pbornemeier

    Why is Columns:=aryDeDupe not the same as Columns:=(aryDeDupe)

    Working to answer this thread: https://www.mrexcel.com/forum/excel-questions/1106189-all-combinations-multiple-columns-without-duplicates.html I needed a way to remove duplicates from a variable number of columns VBA recorded for remove duplicates in H:J of columns A:J is...
  8. JackDanIce

    Evaluate SUMIF error?

    Hi, I have indexed data in K1:K7 and values in L1:L7 (incl. header row) I'd like to extract unique values from K1:K7 into column G and in column H list SUMIF values. I have the following but it returns the value for the first unique value in K2 on all rows in column H. Can someone suggest, I...
  9. P

    If function to evaluate another formula

    I have the following in cell M2: =IF(($J$29-$J$28)<=7,$X$4+$X$2,$X$2) I would like to create the following which references cell M2: =IF(M2<>"1610.59","602.8","") The problem I believe I am having is the 2nd If statement is evaluating a formula, rather than the actual cell value. How do I...
  10. S

    Evaluate any UDF arguments as is, as a cell in excel would do

    How do I evalute myudf as excel evaulates its own in-built functions? many experts has said it is imposible , VBA doesnt have native ability to this, and to basically "Go and learn Python or C" instead. And there are many ways to do this. But I think VBA can do this, becuause some UDF's...
  11. L

    The Evaluate function drops a leading zero

    Hi is there a way to use the VBA Evaluate function and have it retain a leading zero, if the zero is there? I don't want to add leading zeros I just want it to be kept if it already exists. Thanks L
  12. L

    Conditional Formatting Rule Not Working Properly

    I'm trying to conditionally format a column based on three criteria. I'm using the AND function to evaluate three different cells in the same row and if all three are "true" then I want to conditionally format the first of those 3 cells. When I run the AND function down the side in a helper...
  13. B

    Rid of value error

    i am trying to extract alphanumeric data like EXAMPLE: 785abc25d so i tried so please help me i am using excel 2010 version =INDEX(ROW(INDIRECT("1:"&LEN(A36))),N(IF(1,MID(A36,ROW(INDIRECT("1:"&LEN(A36))),1)+0))) after i pressed f9 key to evaluate so it is showing...
  14. T

    Evaluate with SUMPRODUCT and variable

    Hi Everyone! I'm attempting to calculate the below formula in VBA. However, it returns a type mistmatch error. Any help would be greatly appreciated =) dblMyVal = Evaluate("SUMPRODUCT(($H$2:$H$1000)*($A$2:$A$1000=A" & rw & ")*($B$2:$B$1000=B" & rw & ")*($D$2:$D$1000=D" & rw &...
  15. T

    Evaluate with SUMPRODUCT and variable

    Hi Everyone! I'm attempting to calculate the below formula in VBA. However, it returns a type mistmatch error. Any help would be greatly appreciated =) dblMyVal = Evaluate("SUMPRODUCT(($H$2:$H$1000)*($A$2:$A$1000=A" & rw & ")*($B$2:$B$1000=B" & rw & ")*($D$2:$D$1000=D" & rw &...
  16. J

    Evaluate sumifs in VBA

    Good morning all, I'm run into a bit of an issue with a workbook i'm writing. I have a sumif formula pulling in indirect references which works when added to a cell in the workbook; =SUMIFS(INDIRECT($C$16),INDIRECT(VLOOKUP("MONTH",$A$24:$C$28, 3,FALSE)),$F31,INDIRECT(VLOOKUP("Reporting...
  17. Jon von der Heyden

    Parsing a formula: qualify sheet for all range references

    Hi All I have a very large workbook with a large volume of validation rules across multiple sheets. The validation uses custom hence standard formulas that evaluate to either TRUE/FALSE. I would like to extract all of the formulas in to a single worksheet. In doing so I find a problem in...
  18. H

    Using Arrays to calculate Formulas, then writing back to Sheet

    Hi, I'm using a worksheet to get stock quotes, cols A-C contain Ticker, Name and Price, with col C containing the formula below for row 2 (row 1 is a header) where $A2 contains the ticker whose price I want.=VALUE(WEBSERVICE("https://api.iextrading.com/1.0/stock/" & $A2 &...
  19. R

    Sum if not blank

    So I have the following data: A1:A10 all contain 1 (for simplicity) B1 to B10 contains formula that evaluate either to a number or to "" Lets say 8 evaluate to a number (some of which are 0) and 2 evaluate to "" I want to sum all the cells in A for which the corresponding cell in B is not ""...
  20. S

    Evaluate string to formula with link to online documents.

    Dear Sirs, I have one problem with evaluation function. <tbody> Online file name Link made by CONCATENATE funcеion (string) test1 00 123 3123.xlsm "='http://test.test.com/Manufacturing//[test1 00 123 3123.xlsm]Sheet1'!$B2" test2 90 43 4.xlsm "='http://test.test.com/Manufacturing//[test2...

Some videos you may like

This Week's Hot Topics