Application.Evaluate error

[SailePaty]

What I already have is a function to sum, short, etc. by color even if it is coming from a conditional format.

It's working pretty well in English version but I got an error using the below instruction in VBA when Excel is in Spanish version.

[VBA]bValue = Application.Evaluate(Range("A2").FormatConditions(1).Formula1)
[/VBA]
The conditional formula is average(b1,b2) that in Spanish is promedio(b1,b2). However, if the formula promedio is in a cell the following instruction does not give an error.

[VBA]bValue = Application.Evaluate(Range("A2").Formula)
[/VBA]
I mentioned the formula average but it could be whatever formula. The issue is that Application.Evaluate can not handle the formulas inside of the conditional format when Excel is not English version.

Any suggestions would be very appreciate

[Bob Phillips]

It is a bit difficult to be precise as I don't have a foreign language version of Excel, but I would suspect that it is because the Formula1 property returns the Spanish text, and VBA works in pure English (well not pure, American English ).

You could try dumping the formula text into a cell then getting that formula in VBA, it might just get you back to Enlish formula, which uou can use in your VBA code.

[SailePaty]

You're totally right, I already tried that and it works. However, I don't have idea how to setup in which cells write the text when I'm using the function to sort or sum a lot of records.

Any clue?


Thanks

[Norie]

Couldn't you just as has been suggest do something like this?
[vba]
Range("Z65536").Formula = Range("A2").FormatConditions(1).Formula1

bValue = Application.Evaluate(Range("Z65536").Formula) [/vba]

[Bob Phillips]

You're totally right, I already tried that and it works. However, I don't have idea how to setup in which cells write the text when I'm using the function to sort or sum a lot of records.

Do you mean how do you work out whether a cell has a formula conditioanl format? If so, start by lo0oking at http://www.xldynamic.com/source/xld.CFConditions.html

[Norie]

xld

I think the OP want's to actually find out if the conditions are true or not, and then do something.

By the way this has been posted twice on MrExcel (once in the main forum, once in the Internationol forum) and once on OzGrid.

The posting in the International Forum looks interesting, both Greg Truby and Juan Pablo Gonzalez have contributed, but since my Spanish isn't too hot I didn't quite follow it.

[SailePaty]

NORIE: Yes it was me asking the same thing in different forums; unfortunately I didn't get the answer yet. After some answers in the international forum Pablo couldn?t get this work as well.

XLD: I'm going to use one code of the xldynamic's link to ask my next question.

[VBA]'---------------------------------------------------------------------
Public Function IsCFMet2(rng As Range) As Boolean
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

Set rng = rng(1, 1)
If rng.FormatConditions.Count > 0 Then
For Each oFC In rng.FormatConditions
If oFC.Type = xlExpression Then
're-adjust the formula back to the formula that applies
'to the cell as relative formulae adjust to the activecell
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
IsCFMet2 = rng.Parent.Evaluate(sF1)
End If
If IsCFMet2 Then Exit Function
Next oFC
End If
End Function[/VBA]
If I?m right your suggestion is to replace the line highlighted in red by the following lines:
[VBA]
Range("Z65536").Formula = sF1
ISCFMet2 = Application.Evaluate(Range("Z65536").Formula).
[/VBA]

My question is, Does it not matter I?m calling the function IsCFMet2 in different cells?
Thanks so much for your help

[Bob Phillips]

XLD: I'm going to use one code of the xldynamic's link to ask my next question.

If I?m right your suggestion is to replace the line highlighted in red by the following lines:

This is a very difficult one to answer as I don't have an international version of Excel to try out what I am saying, but I will give it a shot.

Essentially, that formula looks at the first cell in a range, and if it has CF and is using an expression, that is Condition is Formula Is, then it evaluates that formula to see if it is True, the condition is met. So, in short, yes I thinks that that is what you should do. I would be interested to hear back what happens.

My question is, Does it not matter I?m calling the function IsCFMet2 in different cells?

This is where it gets tricky. If you are looking to use this within a worksheet function, you can't. Changing a worksheet does not work in a UDF, so the line

Range("Z65536").Formula = sF1

will fail (BTW, better to use Cells(Rows.Count,Columns.Count).Formula = sF1). IsCFMet2 works as a UDF because it just uses evaluate, your amended version won't I am afraid.

[Ken Puls]

By the way this has been posted twice on MrExcel (once in the main forum, once in the Internationol forum) and once on OzGrid.

The posting in the International Forum looks interesting, both Greg Truby and Juan Pablo Gonzalez have contributed, but since my Spanish isn't too hot I didn't quite follow it.

Hi there,

I have nothing against cross posting to get an answer, but SailePaty please respect that you are consuming volunteer time that you are getting for free. Many of these people charge big dollars for consulting, so you are truly getting a valuable service here.

Rather than have the whole world work on your problem unaware of the others' efforts, please post a link if you cross post. Board experts tend to follow the links to see what others have done, and that saves them reinventing the wheel. Most won't mind a cross post at all (although some will), but most are quite offended when they find out they've been working on a solution when someone else has already provided one.

[SailePaty]

Hi XLD,

I am out of the town the rest of the week. Since I don?t have Excel Spanish version instaled in this computer I will test your suggestion as soon I get at home.

I really appreciated all your effort and support.

KPULS:

I understand your point and it wouldn?t happen again and I?m sorry you got mad at me, as well. However, try to be less rude the next time.

Thanks again for your help.

[Norie]

SailePaty

Kpuls isn't 'mad' at you and I don't think he/she is being rude.

It's more that you are actually being rude by posting all over the place.

As far as I know, at all the forums you have posted to the members give free advice, on their time, for no charge.

If you want a perfect answer to your problem, then either pay for it or give us some more information.

[Bob Phillips]

SailePaty

Kpuls isn't 'mad' at you and I don't think he/she is being rude.

It's more that you are actually being rude by posting all over the place.

As far as I know, at all the forums you have posted to the members give free advice, on their time, for no charge.

If you want a perfect answer to your problem, then either pay for it or give us some more information.

I would also like to state that Ken was neither mad at you, nor was he rude in any way that I can read.

Ken is an administrator of this board, and as such he is almost duty-bound to point out to you that the practice that you were entering into is at best frowned upon, and would get your post pulled if discovered on some boards. That he did it in such a reasoned, balanced manner is a credit to him and his command of the language.

Believe me I am not coming down on you, but I do believe you have mis-read Ken's post.

[SailePaty]

Hi Guys,

I?m sure there a couple of misinterpretations, so thanks for the clarification.

By the way XLD, I just reread your last propose and unfortunately I have to look a different way to resolve this while I?m using IsCFMet2 as a UDF function.


I would let you know how I got this work.

Thanks for your help.