Search:

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

Page 1 of 6 1

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

by Peter_SSs
Replies
36
Views
618

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. Thread: Create a summary list

by Peter_SSs
Replies
16
Views
98

Re: Create a summary list

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

by Peter_SSs
Replies
36
Views
618

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

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

by Peter_SSs
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. Thread: Format the 3 numbers in a column closest to ZERO

by Peter_SSs
Replies
36
Views
618

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. Thread: Create a summary list

by Peter_SSs
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. :)

by Peter_SSs
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. Thread: Format the 3 numbers in a column closest to ZERO

by Peter_SSs
Replies
36
Views
618

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. Thread: Format the 3 numbers in a column closest to ZERO

by Peter_SSs
Replies
36
Views
618

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

by Peter_SSs
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...

by Peter_SSs
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. Thread: Format the 3 numbers in a column closest to ZERO

by Peter_SSs
Replies
36
Views
618

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

Cheers. 8-)

by Peter_SSs
Replies
3
Views
107

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

by Peter_SSs
Replies
36
Views
618

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

by Peter_SSs
Replies
3
Views
107

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. Thread: Format the 3 numbers in a column closest to ZERO

by Peter_SSs
Replies
36
Views
618

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))

by Peter_SSs
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,...

by Peter_SSs
Replies
9
Views
182

so..

by Peter_SSs
Replies
9
Views
182

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

by Peter_SSs
Replies
9
Views
182

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

by Peter_SSs
Replies
5
Views
130

Re: need to "read" value not formula

Glad you got something that works for you. Thanks for letting us know.

by Peter_SSs
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. Thread: Format the 3 numbers in a column closest to ZERO

by Peter_SSs
Replies
36
Views
618

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. Thread: Insert blank rows when field value changes

by Peter_SSs
Replies
3
Views
140

Re: Insert blank rows when field value changes

You are very welcome. Glad it worked for you. Thanks for letting us know. :)
25. Thread: Changing Characters within Formula

by Peter_SSs
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