Formula in CF

D

dlamarche

Hello I know that I can do a formula like:

$B2>$K$1 in the formula box to apply a DF all cells in column B where the
value is higher than the value in K1. Good. Now what I was asked to do is to
put the comparison operator in a cell ... as well!

This way the user can change the number in K1 but the conditional operator
too which, say, is in K2. So K1 contains 3500 and K2 contains > and I want to
do a formula in the CF formula box. The user can change the value in K1 and
type > or < in K2 and the CF will update.

It is interesting that in any cell I can to a simple concatenation like
"="&$B2&$K$2&$K$1 and pressing Enter displays

=4500>6000

I have played with indirect a bit to create a reference in a concatenation.
It seems that CF can only accept a real formula and not a one built as the
result of an expression.

Any idea? Thank you.
 
C

Charabeuh

Try this:

in a module copy this VBA function:
---------------------------------------
Public Function Compute(x, y, z)
Compute = Application.Evaluate("=" & x & y & z)
End Function
---------------------------------------

In the conditionnal format dialog box, enter the formula:
=Compute(B1,$K$1,$K$2)

with B1 is the cell to format
$K$1 is for example >
$K$2 is for example 3500
 
C

Charabeuh

Prefer this function :
-----------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute_n = Application.Evaluate(xS)
End Function
 
C

Charabeuh

too fast !!!! prefer this function:
------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute = Application.Evaluate(xS) '<== here the error was...
End Function
 
D

Dana DeLouis

xS = "=" & " " & x ...etc

As a side note, "=" is usually not required to "Evaluate"

Public Function Compute(ParamArray v())
Compute = Evaluate(Join(v))
End Function


=Compute(A1,B1,C1)

= = = =
Dana DeLouis

too fast !!!! prefer this function:
------------------------
Public Function Compute(x As Range, y As Range, z As Range)
Dim xS As String
Application.Volatile
xS = "=" & " " & x.Address & " " & y.Value & " " & z.Address
Compute = Application.Evaluate(xS) '<== here the error was...
End Function
 
C

Charabeuh

Hello,
As a side note, "=" is usually not required to "Evaluate"
Thanks! I will keep it in mind.

==> Why I prefer : x.Address & " " & y.Value & " " & z.Address
instead of : x & y & z

I have a french excel 2007 and use the "," for decimal numbers
if x=3,5 and y =< and z = 5 then x & y & z gives : 3,5<5
When VBA evaluate this, the result is an erreur of value.
VBA only understand american forms of decimals.
When the formula uses addresses the result is right.
the formula becomes : A1 < C1 that is correctly evaluated.
This formula should work with either the '.' or ',' for decimal numbers.

I could also change my regional options to use the '.' instead
of ",' for decimals to use Evaluate(Join(v))





Dana DeLouis said:
xS = "=" & " " & x ...etc

As a side note, "=" is usually not required to "Evaluate"

Public Function Compute(ParamArray v())
Compute = Evaluate(Join(v))
End Function


=Compute(A1,B1,C1)

= = = =
Dana DeLouis
 
D

Dana DeLouis

I have a french excel 2007 and use the "," for decimal numbers
When the formula uses addresses the result is right.

Thanks. I learned something new. :>)

Dana
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top