Hi there,

IS it possible to select all cells on the active sheet that contains
constants in my formulas?

EG:
=A1+A2 = do not select
=A1+A2+100 = select
=100*A1-A2 =select

(=A1+Max(3,4)= not select?!)



Quoting Ivan Maola's moto: Can do!
I hope you can show me ... :o)
Brgds Sige

Hi Sige,

Try something like:

'=================
Sub Tester01()
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long

arr = Array("/", "~*", "+", "-", "", "<", "=", "^")

On Error Resume Next 'In case no formulas!
Set rng = ActiveSheet.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

rng2.Select

End Sub
'<<=================

---
Regards,
Norman



"Sige" wrote in message
oups.com...
Hi there,

IS it possible to select all cells on the active sheet that contains
constants in my formulas?

EG:
=A1+A2 = do not select
=A1+A2+100 = select
=100*A1-A2 =select

(=A1+Max(3,4)= not select?!)



Quoting Ivan Maola's moto: Can do!
I hope you can show me ... :o)
Brgds Sige

Wonderfull Norman!!!

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

*-*-*-*-*-*-*-*-*-*-*
=100/+-A1 = OK
=A1/+-100 = OK

:o) Sige

Hi Sige,

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

Change:

arr = Array("/", "~*", "+", "-", "", "<", "=", "^")

to:

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]")


---
Regards,
Norman



"Sige" wrote in message
ps.com...
Wonderfull Norman!!!

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

*-*-*-*-*-*-*-*-*-*-*
=100/+-A1 = OK
=A1/+-100 = OK

:o) Sige

Hi Sige,

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]")

To add, the multiplication operator '*' is enclosed in square brackets so
that the Like operator can distingish it as a unique character, and not as
the Like operator's '*' wildcard character.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Sige,

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

Change:

arr = Array("/", "~*", "+", "-", "", "<", "=", "^")

to:

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]")


---
Regards,
Norman



"Sige" wrote in message
ps.com...
Wonderfull Norman!!!

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

*-*-*-*-*-*-*-*-*-*-*
=100/+-A1 = OK
=A1/+-100 = OK

:o) Sige

Hi Norman,

To add, the multiplication operator '*' is enclosed in square brackets so
that the Like operator can distingish it as a unique character, and not as
the Like operator's '*' wildcard character.

Devious. Wouldn't you need to add "(" and ")" too?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Jan Karel,

Devious. Wouldn't you need to add "("

I agree that the left bracket character "(" should be added to the array.

and ")" too?

If the opening bracket is captured, perhaps testing for the corresponding
closing bracker becomes superfluous?


---
Regards,
Norman



"Jan Karel Pieterse" wrote in message
...
Hi Norman,

To add, the multiplication operator '*' is enclosed in square brackets so
that the Like operator can distingish it as a unique character, and not
as
the Like operator's '*' wildcard character.

Devious. Wouldn't you need to add "(" and ")" too?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Norman,

If the opening bracket is captured, perhaps testing for the corresponding
closing bracker becomes superfluous?

Dunno, what if the formula is

=sin(A1+100)

Wouldn't you need the closing paren? (OK, I didn't test <g)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Hi Jan Karel,

Dunno, what if the formula is

=sin(A1+100)

Wouldn't you need the closing paren? (OK, I didn't test <g)

Your example would be caught in:

. sStr = "*" & arr(i) & "[0-9]*"

using the array value of "+" for i.


---
Regards,
Norman



"Jan Karel Pieterse" wrote in message
...
Hi Norman,

If the opening bracket is captured, perhaps testing for the corresponding
closing bracker becomes superfluous?

Dunno, what if the formula is

=sin(A1+100)

Wouldn't you need the closing paren? (OK, I didn't test <g)

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Can someone please help me with this one :

I want my formula to sum all there is in column "F" only if its colum
B=1 and its column C<10000 :

SUM.IF (AND(B2:B43 = 1);(C2:C43<10000); (F2:F43))

What is the right way to write this formula ???

Thanks a lot !!

--
Mimin
-----------------------------------------------------------------------
Mimine's Profile: http://www.excelforum.com/member.php...fo&userid=2745
View this thread: http://www.excelforum.com/showthread.php?threadid=46981

Hi Norman,

Your example would be caught in:

. sStr = "*" & arr(i) & "[0-9]*"

using the array value of "+" for i.

Duh, of course.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Thanks a lot AGAINNNNN Norman!!!

I swear ... this helps me saving hours of search work a month!! People
tampering the spreadsheets adjsuting the budgets etc etc...

Jan Karel: Here you go for your Flexfind ;o))))

Brgds Sige

SUMPRODUCT(--(B2:B43 = 1);--(C2:C43<10000); F2:F43)


--
HTH

Bob Phillips

"Mimine" wrote in
message ...

Can someone please help me with this one :

I want my formula to sum all there is in column "F" only if its column
B=1 and its column C<10000 :

SUM.IF (AND(B2:B43 = 1);(C2:C43<10000); (F2:F43))

What is the right way to write this formula ???

Thanks a lot !!!


--
Mimine
------------------------------------------------------------------------
Mimine's Profile:
http://www.excelforum.com/member.php...o&userid=27456
View this thread: http://www.excelforum.com/showthread...hreadid=469813

Hi Sige,

You are welcome.

Jan Karel: Here you go for your Flexfind ;o))))

I agree wholeheartedly - but have you looked at JKP's other addins?


---
Regards,
Norman



"Sige" wrote in message
oups.com...
Thanks a lot AGAINNNNN Norman!!!

I swear ... this helps me saving hours of search work a month!! People
tampering the spreadsheets adjsuting the budgets etc etc...

Jan Karel: Here you go for your Flexfind ;o))))

Brgds Sige

Hi Sige,

Jan Karel: Here you go for your Flexfind

I think this one is best kept outside of that, too much of a "special".
Useful though.

Norman: care to write a page for my site?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

Sure Norman,

The Autosafe ... is helping me everyday!

Especially with XL97 -without recovery tool- this comes in veryyyy
handy while trying out codes on days like this!

Sige

Norman,

Would it be possible to highlight it in a color? ...

This is too trivial I know ... But how to UNDO the highlighting?

Sige

Hi Sige,

Try:
'=================
Sub HighlightConstantFormulae(Optional aColor As Long = 6)
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(")
On Error Resume Next 'In case no formulas!
Set rng = ActiveSheet.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

rng2.Interior.ColorIndex = aColor

End Sub
'<<=================

Usage:

'=================
Sub TestIt()
HighlightConstantFormulae ' To Highlight in colour

'OR uncomment the next line
' HighlightConstantFormulae 0 ' To remove Highlight

End Sub
'<<=================

Change Optional aColor As Long = 6 to a colour of your choice.

---
Regards,
Norman



"Sige" wrote in message
oups.com...
Norman,

Would it be possible to highlight it in a color? ...

This is too trivial I know ... But how to UNDO the highlighting?

Sige

Hi Sige,

Additionally, you could assign the following to a button to toggle
highlighting:

'=================
Sub Toggle()
Static aColor As Long

aColor = IIf(aColor = 6, 0, 6)
HighlightConstantFormulae aColor

End Sub
'<<=============

Again, change 6 to taste.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Sige,

Try:
'=================
Sub HighlightConstantFormulae(Optional aColor As Long = 6)
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(")
On Error Resume Next 'In case no formulas!
Set rng = ActiveSheet.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

rng2.Interior.ColorIndex = aColor

End Sub
'<<=================

Usage:

'=================
Sub TestIt()
HighlightConstantFormulae ' To Highlight in colour

'OR uncomment the next line
' HighlightConstantFormulae 0 ' To remove Highlight

End Sub
'<<=================

Change Optional aColor As Long = 6 to a colour of your choice.

---
Regards,
Norman



"Sige" wrote in message
oups.com...
Norman,

Would it be possible to highlight it in a color? ...

This is too trivial I know ... But how to UNDO the highlighting?

Sige

Hi Norman,

Thanks again. It works fine ...but I was more looking for a Ctrl
+z-function.

Undoing the coloring but returning to the previous coloring if there
was one ...
I do not know whether this is easy ?!

Sige

Hi Sige,

Try:

'=================
Sub HighlightConstantFormulae(Optional aColor As Long = 6)
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(")
On Error Resume Next 'In case no formulas!
Set rng = ActiveSheet.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

With rng2
.FormatConditions.Delete
If aColor 0 Then
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = aColor
End If
End With

End Sub
'<<=================
'=================
Sub Toggle()
Static aColor As Long

aColor = IIf(aColor = 6, 0, 6)
HighlightConstantFormulae aColor

End Sub
'<<=============


---
Regards,
Norman



"Sige" wrote in message
oups.com...
Hi Norman,

Thanks again. It works fine ...but I was more looking for a Ctrl
+z-function.

Undoing the coloring but returning to the previous coloring if there
was one ...
I do not know whether this is easy ?!

Sige

Hi Sige,

Undoing the coloring but returning to the previous coloring if there
was one ...

I should add, that the suggested code assumes that the previous coloring is
not the result of conditional formatting.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Sige,

Try:

'=================
Sub HighlightConstantFormulae(Optional aColor As Long = 6)
Dim rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(")
On Error Resume Next 'In case no formulas!
Set rng = ActiveSheet.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

With rng2
.FormatConditions.Delete
If aColor 0 Then
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = aColor
End If
End With

End Sub
'<<=================
'=================
Sub Toggle()
Static aColor As Long

aColor = IIf(aColor = 6, 0, 6)
HighlightConstantFormulae aColor

End Sub
'<<=============


---
Regards,
Norman



"Sige" wrote in message
oups.com...
Hi Norman,

Thanks again. It works fine ...but I was more looking for a Ctrl
+z-function.

I do not know whether this is easy ?!

Sige

No Previous coloring is not conditional formatting!

Sige

It is magnificent!

It's just that my previous color cell is gone ... (possible conditional
formattting remains un-touched)

Sige

Ooops,
Did not say anything ...missed this one out!
Wonderful wonderful!



With rng2
.FormatConditions.Delete
If aColor 0 Then
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = aColor
End If
End With

Norman,

Could it be tested that rng has conditional formatting ...?

if TRUE then
msgbox Current conditional formatting will be deleted, continue?
vbYesNo

Sige

Hi Sige,

Try:
'=================
Sub HighlightConstantFormulae(Optional aColor As Long = 6)
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rCell As Range
Dim arr As Variant
Dim sStr As String
Dim i As Long
Dim res As Long
Static blCFdeleted

arr = Array("/", "~*", "+", "-", "", "<", "=", "^", "[*]", "(")
On Error Resume Next 'In case no formulas!
Set rng = ActiveSheet.UsedRange.SpecialCells(xlFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
For Each rCell In rng.Cells
For i = LBound(arr) To UBound(arr)
sStr = "*" & arr(i) & "[0-9]*"
If rCell.Formula Like sStr Then
If Not rng2 Is Nothing Then
Set rng2 = Union(rng2, rCell)
Else
Set rng2 = rCell
End If
End If
Next i
Next rCell
Else
'No formulas found
End If

On Error Resume Next
Set rng3 = _
rng2.SpecialCells(xlCellTypeAllFormatConditions)
On Error GoTo 0

If Not rng3 Is Nothing Then
If Not blCFdeleted Then
res = MsgBox("Current conditional formatting will be " & _
"deleted, continue?", _
Buttons:=vbYesNo)
End If
End If

If res = vbYes Then aColor = 6

If Not res = vbNo Then
With rng2
.FormatConditions.Delete
blCFdeleted = True
If aColor 0 Then
.FormatConditions.Add Type:=xlExpression, _
Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = aColor
End If
End With
End If

End Sub
'--------------------------------

Sub Toggle()
Static aColor As Long

aColor = IIf(aColor = 6, 0, 6)
HighlightConstantFormulae aColor

End Sub
'<<=============

---
Regards,
Norman



"Sige" wrote in message
oups.com...
Norman,

Could it be tested that rng has conditional formatting ...?

if TRUE then
msgbox Current conditional formatting will be deleted, continue?
vbYesNo

Sige

Hi Norman,

So far the enhancements!
For me this is an EXTREMELY useful tool....
I ll put it in my personal add-in!

I don't think you have a personal website ... (do you?) but if you get
one, put it on!
A lot of controllers, planners,... and other number crunchers will
benefit from it!

Deep bow,
Sige