Search:

Type: Posts; User: Peter_SSs; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.84 seconds.

  1. Re: Format the 3 numbers in a column closest to ZERO

    OK, that makes more sense. :)

    Try something like this

    =IF(R6="","",SUMPRODUCT((ABS(R6)>ABS(R$6:R$1000))+0)+1-COUNTIF(R$6:R$1000,""))
  2. Replies
    16
    Views
    98

    Re: Create a summary list

    You're welcome. Glad it worked for you. :)
  3. Re: Format the 3 numbers in a column closest to ZERO

    I don't follow the example.
    - In the top part there are 8 numbers but in the rankings you have 9 numbers.
    - In the rankings you have two '1' values but it doesn't seem that you have two numbers...
  4. Replies
    3
    Views
    40

    Re: COUNTIF statement

    Are the values in column H actual numbers, or text?
    What happens if you put this in a blank cell on the data sheet? =ISNUMBER(H2)
  5. Re: Format the 3 numbers in a column closest to ZERO

    In post #24 you said you were using the formula in Conditional Formatting. Are you now saying that is not the case and this is being used a s a standard formula in its own cell/column?
  6. Replies
    16
    Views
    98

    Re: Create a summary list

    How do you calculate that? The BA values are ..
    ($617.90)
    ($374.06)
    ($101.09)
    $36.92
    $933.90
    .. which to me totals -122.23

    Edit: Ignore above as I see you have edited your post. :)
  7. Replies
    3
    Views
    40

    Re: COUNTIF statement

    Something like this?

    <b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color:...
  8. Re: Format the 3 numbers in a column closest to ZERO

    So, why not use the CF formulas from post #11 ?

    <b>CF Near Zero (3)</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt;...
  9. Re: Format the 3 numbers in a column closest to ZERO

    1. In words, what is this formula supposed to be doing?
    2. Is it being used in Conditional Formatting like the previous ones?
    3. What cell(s) is the formula applied in?



    4. Do you mean...
  10. Replies
    23
    Views
    684

    Re: Substring Search !

    See if this is any use.

    Sub CheckAllSubInOneMain()
    Dim MainString As String, Substring As String, TestString As String
    Dim MainString1 As String, MainString2 As String, MainString3 As...
  11. Replies
    23
    Views
    684

    Re: Substring Search !

    So, are you saying that all sections of Substring must be found within, say, Mainstring1 or all found within, say, Mainstring3 for a "True" result?

    So If 2 sections are found in Mainstring1 and 1...
  12. Re: Format the 3 numbers in a column closest to ZERO

    Cheers. 8-)
  13. Replies
    3
    Views
    107

    Re: Formula Help Needed please

    You're welcome. Thanks for the follow up. :)
  14. Re: Format the 3 numbers in a column closest to ZERO

    It is working for me as is ...

    <b>CF Near 50</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff;...
  15. Replies
    3
    Views
    107

    Re: Formula Help Needed please

    Welcome to the MrExcel Board!

    Something like this, copied down?

    <b>Stock Expiry</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt;...
  16. Re: Format the 3 numbers in a column closest to ZERO

    Try something like
    =ISNUMBER(A1)*(ABS(A1-50)<=AGGREGATE(14,6,ABS(A$1:A$1000-50)*(A$1:A$1000<>""),COUNT(A$1:A$1000)-2))
  17. Replies
    23
    Views
    684

    Re: Substring Search !

    If I have understood the requirement correctly there should still be no need for the dictionary or the loop. Try something like this.

    Sub CheckAllSubInAnyMain()
    Dim MainString As String,...
  18. Re: Delete certain email addresses from a column?

    so..
  19. Re: Delete certain email addresses from a column?

    No, that shouldn't matter.
    Perhaps you need to give us a small set of sample data and the expected results.

    Here is my sample data ...

    <b>Excel 2016</b><table cellpadding="2.5px" rules="all"...
  20. Re: Delete certain email addresses from a column?

    Give this a try in a copy of your workbook.


    Sub Del_Rows()
    Dim a As Variant, b As Variant
    Dim nc As Long, i As Long, k As Long

    Const sEmailCol As String = "A" 'Column with email...
  21. Replies
    5
    Views
    130

    Re: need to "read" value not formula

    Glad you got something that works for you. Thanks for letting us know.
  22. Replies
    5
    Views
    130

    Re: need to "read" value not formula

    A Worksheet_Change event is never triggered by a formula cell changing its value. Your AB6 always contains the same formula so the Worksheet_Change event does not see that cell a ever changing...
  23. Re: Format the 3 numbers in a column closest to ZERO

    .. but note that my latest suggestion uses the second argument of '6' (DO ignore error values)
  24. Re: Insert blank rows when field value changes

    You are very welcome. Glad it worked for you. Thanks for letting us know. :)
  25. Replies
    7
    Views
    265

    Re: Changing Characters within Formula

    I don't have your sheet/formulas to test with but that code looks okay to me, though you don't have to repeat all of it. This works for my test sheet.


    Private Sub CommandButton15_Click()
    ...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4