• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recent content by Colin Legg

  1. Colin Legg

    Case Sensitive in SUMIF

    SUMIF text comparisons are case insensitive so this cannot be achieved per your requirements. You either need to use a helper column or an array/SUMPRODUCT formula.
  2. Colin Legg

    Welcome back SirJB7

    Welcome back @SirJB7! It's good to see you on here again.
  3. Colin Legg

    New Chandoo.org Excel Ninja's appointed

    Congratulations to all of you!
  4. Colin Legg

    Error in VBA code Odd Even Function.

    Hi, Firstly, the second line in your code has a typo: it's really important to use Option Explicit to pick mistakes like that up. Secondly, your code is missing 2 functions: IsEven() and IsOdd(). The VBA object model does not have IsEven() and IsOdd() functions so you have to either implement...
  5. Colin Legg

    Worksheet ActiveX controls make my file crash - How to avoid/fix?

    Microsoft security update MS14-082 is known to cause the error message you describe. See more info here.
  6. Colin Legg

    COUNTIF is not picking up a ":"

    My guess from the OP response is that the requirement is to count the number of colons which occur in a range. @DashboardNovice COUNTIF, such as the formula suggested by Faseeh, will count the number of cells which contain a ":". If you want to count the actual number of colons then you can...
  7. Colin Legg

    Finding the position of the nth character in a cell

    So I think the generalised version of my formula would be: =IFERROR(REPLACE( [STRING] ,1,SEARCH("||",SUBSTITUTE( [STRING] , [CHAR] ,"||", [N] )),""), [STRING] ) Where [STRING] is the whole string being evaluated [CHAR] is the character you want to find [N] is the nth occurrence you of the...
  8. Colin Legg

    Finding the position of the nth character in a cell

    Try this formula: =IFERROR(REPLACE(B4,1,SEARCH("||",SUBSTITUTE(B4,":","||",LEN(B4)-LEN(SUBSTITUTE(B4,":","")))),""),B4)
  9. Colin Legg

    VBA code to run SQL queries in Oracle SQL Developer 1.5.1

    There are lots of ways to do this but, by the sounds of it, the answer you're probably looking for is to use ADO to run queries against the database
  10. Colin Legg

    VBA jumps to a function for no apparent reason ...

    If your SumIntervalCols() function is being used in formula(s) and th(os)e formula(s) it is used in has a dependency on the range changed by your code snippet (somewhere around Summary!A9) then it will cause th(os)e formula(s) to recalculate and your function to be called.
  11. Colin Legg

    Congratulations Hui Sir ! 7000+

    Congratulations!
  12. Colin Legg

    syntax error on sum in sql using Excel VBA

    It should be sum([Amount]) rather than sum[Amount]. Additionally, when you use an aggregator function such as SUM in SQL you need to provide a GROUP BY clause. You also need to drop the DISTINCT keyword. As I mentioned on your other thread (which seems to be very similar), you shouldn't use ADO...
  13. Colin Legg

    error on SQL using Excel VBA

    What is the actual error message? By the way, you shouldn't use ADO to query the workbook the code is in: querying open workbooks with ADO causes a memory leak.
  14. Colin Legg

    How to sum 3 cells having array formulas ignoring #N/A error

    AGGREGATE() is super if you have XL 2010 or later. These will also work in any version and don't need CSE: =SUM(SUMIF(E13:G13,{"<0",">0"})) =SUMIF(E13:G13,"<="&9.99E307)
  15. Colin Legg

    Excel Add-In Installer

    As Narayan indicated with the Excel Guru link, ideally you would want to save the add-in onto a network share and let the users use it from there. When they install the add-in make sure that they do not choose to copy the add-in locally. If you wanted to you could give them a little VBA code to...
Back
Top