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

Search results

  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...
  16. Colin Legg

    Run .bat file from VBA

    @Berty Sharing the folder and sorting out the permissions is all done manually. Then the Shell command would be like: Shell "\\YourServer\SharedFolder\Script.bat"
  17. Colin Legg

    Run .bat file from VBA

    If you want to run the bat file on the server from your local machine then you'll need to share the folder it is in and then give yourself permissions to that share. Then in the Vba code you use the server name or DNS alias to qualify the full path to the file. If this is a task which can simply...
  18. Colin Legg

    Speed of Sub vs. Function when not returning a value?

    It won't make any difference to performance.
  19. Colin Legg

    SUMIFS based on a list

    Sure, no problem. You are right to be generally sceptical about the efficiency of SUMPRODUCT() formulas. If you're interested in some benchmarking in different sumproduct variations on this style of conditional sum, have a look here.
  20. Colin Legg

    SUMIFS based on a list

    Why without SUMPRODUCT? If you are worried about slow calculations, the formula I gave you is almost exactly as efficient as your original formula but written more concisely. If your original formula is fine in terms of calculation speed then this one will be fine too. This is because the...
  21. Colin Legg

    Confusion between Static variables & Constant variables.

    I would go one step further and suggest that one should explicitly define the constant's data type. Constants' values cannot be changed at runtime. Variables' values can be changed at runtime. Static variables are procedure level variables which retain their values between procedure calls.
  22. Colin Legg

    SUMIFS based on a list

    Like this: =SUMPRODUCT(SUMIFS(APR14.DATA!$O:$O,APR14.DATA!$Z:$Z,$C11,APR14.DATA!$X:$X,'P&L CATS'!$A$1:$A$4))
  23. Colin Legg

    Congrats Shrivallabha for 1000 Post

    Many congratulations, Shrivallabha. :)
  24. Colin Legg

    Customizing Excel Office Theme color

    I mean the colour/shading Excel gives to indicate which cells are selected, not the cell fill colour. I blogged about it here. Prior to XL 2013 it was controlled from the Windows appearance settings.
  25. Colin Legg

    Customizing Excel Office Theme color

    Hi, No, you cannot customise it with your own colour scheme. In Excel 2013 you cannot even customise the colour of selected cells.
Back
Top