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

    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...
  5. 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...
  6. 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...
  7. 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
  8. 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...
  9. 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...
  10. 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 &...
  11. 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 &...
  12. 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...
  13. 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...
  14. 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 &...
  15. 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 ""...
  16. 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...
  17. S

    Evaluate column and replace existing values

    Hi, I am trying to have a macro evaluate column D which has dates listed, however, several in which are 1/0/1900 which I want to replace with nothing. Does anyone have any suggestions?
  18. S

    Using a Named Range to Evaluate("INDEX(PROPER(

    Sorry, but I couldn't hit on the correct search term(s) for any previously posted answers. Is it possible to replace the range address' in the code with the named range? Obviously nothing I've tried has worked, it converts everything in the range to "#Value" Sub m_MakeProper() '2/8/2018...
  19. B

    Extracting the URL form hyperlinks created from formula

    I am using the HYPERLINK function to create a dynamic URL using a formula. As a simplified example, it would look something like this: =HYPERLINK("http://baseurl.com/"&4+4, "testlink8") I can use the UDF below to successfully extract the text portion of the URL but of course it does not...
  20. B

    Ensuring the function "Evaluate(Name)" is using the correct workbook when multiple workbooks are open

    Hello Excel Geniuses. Every time I log in I learn how much I don't know about Excel. Thanks to you all. Here's my latest conundrum. I have a workbook template that is populated externally. When this template is opened, VBA code uses takes that data and organizes it, adds formulas, populate...

Some videos you may like

This Week's Hot Topics