Constant in Formula

[Sige]

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 ... )
Brgds Sige

 

[Norman Jones]

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
'<<=================

 

[Sige]

Wonderfull Norman!!!

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

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

) Sige

 

[Norman Jones]

Hi Sige,

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60
Change:

to:

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

 

[Norman Jones]

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

Hi Sige,

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60
Change:

to:

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


---
Regards,
Norman

Wonderfull Norman!!!

Possible to catch these as well?

=60*A1 gets trapped but not

=A1*60

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

) Sige

 

[Jan Karel Pieterse]

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

 

[Norman Jones]

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?

 

[Jan Karel Pieterse]

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

 

[Norman Jones]

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.

 

[Mimine]

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

 

[Jan Karel Pieterse]

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

 

[Sige]

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

 

[Bob Phillips]

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

 

[Norman Jones]

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?

 

[Jan Karel Pieterse]

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

 

[Sige]

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

 

[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

 

[Norman Jones]

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.

 

[Norman Jones]

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

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

Norman,

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

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

Sige

 

[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