Constant in Formula

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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