Evaluate IF(AND is erasing all values

[johnnyL]

Trying to get Evaluate(IF(AND to work.

Code that works:

Sub TestEvaluateIF()
'
' Check values in A1:A10
'
    With Range(Cells(1, 1), Cells(10, 1))
        .Value = Evaluate("IF(" & .Address & "= ""Good"", ""OK""," & .Address & ")")        '   If cell in Column A range = 'Good' then change Column A
'                                                                                           '           cell to 'OK' Else leave the value as is
    End With
End Sub

Code that erases all values from Column A:

Sub TestEvaluateIF_And()
'
' Check values in A1:B10 via offset ... This erases all values from Column A for some reason?
'
    With Range(Cells(1, 1), Cells(10, 1))
        .Value = Evaluate("IF(AND(" & .Address & "= ""Definitely"", " & _
                .Offset(, 1).Address & "= ""Good""), ""OK""," & .Address & ")")             '   If cell in Column A range = 'Definitely' & cell
'                                                                                           '           in Column B range = 'Good' then change
'                                                                                           '           Column A cell to 'OK' Else leave the value as is
    End With
End Sub

Book1
	A	B	C
1	Definitely	Good
2	Definitely	Good
3	Maybe	Bad
4	Maybe	Good
5	Maybe	Bad
6	Definitely	Good
7	Definitely	BAD
8	Maybe	BAD
9	Definitely	Good
10	Definitely	Good
11
Sheet1

Before I smash this computer, Please let me know what I am messing up.

 

[My Aswer Is This]

Please do not do that.
Before I smash this computer, Please let me know what I am messing up

I can write you a script to do what you want but I never use Evaluate
Not sure I understand it

If doing what you want is Ok to do my way tell me in words what you attempting to do.
I do not understand the script your showing.
Doing it my way may take 5 more seconds then using Evaluate if you can spare a few more seconds.
Depending if your using 800 rows or 800 thousand rows

 

[johnnyL]

Thank you for responding @My Aswer Is This.

I do not understand the script your showing.

I left comments to the right of the code to clearly explain what my intentions are. The first code works as I intended. The 2nd code doesn't.

I can write you a script to do what you want but I never use Evaluate
Not sure I understand it

I am certainly not a pro on 'Evaluate'. From my understanding, it is a way to process a worksheet formula in VBA without having to write the formula to the sheet.


The goal here, as the code demonstrates, is to evaluate the formulas mentioned, (the IF formula that works & the IF(AND formula ... that is not working) without having to loop through cells or an array.


Edit: Here is code for the EvaluateIF formula that works that can be run on the example data that I posted:

Sub TestEvaluateIFV2()
'
' Check values in B1:B10
'
    With Range(Cells(1, 2), Cells(10, 2))
        .Value = Evaluate("IF(" & .Address & "= ""Good"", ""OK""," & .Address & ")")        '   If cell in Column A range = 'Good' then change Column A
'                                                                                           '           cell to 'OK' Else leave the value as is
    End With
End Sub

 

[6StringJazzer]

I thought what you were doing was pretty clear, after analyzing your formula and reading your comments. But I would tend to write a loop to do it in VBA rather than use Evaluate, just because I think it makes it more explicit in the code what you are trying to do. It doesn't mean there is anything wrong with your approach.

OTOH I have analyzed what you've done and I'm not sure why it doesn't work. I'm still trying to figure it out. I suspect it may be because your formula has to be an array formula--I do not know if Evaluate will automatically treat it as such. However, that does not explain why the first bit of code does work. It seems like they should both work, or both not work. Still troubleshooting.

EDIT: When I use your second formula in a cell, I get an array formula that gives correct results. So the logic is OK.

 

[6StringJazzer]

To wit, your original data in A:B and array formulas in D1 and E1. However, the one in E1 does not give correct results. It merely returns the original data.

$scratch.xlsm
	A	B	C	D	E
1	Definitely	Good		Definitely	Definitely
2	Definitely	Good		Definitely	Definitely
3	Maybe	Bad		Maybe	Maybe
4	Good	Good		OK	Good
5	Maybe	Bad		Maybe	Maybe
6	Definitely	Good		Definitely	Definitely
7	Definitely	BAD		Definitely	Definitely
8	Maybe	BAD		Maybe	Maybe
9	Definitely	Good		Definitely	Definitely
10	Definitely	Good		Definitely	Definitely
Good Bad

Cell Formulas
Range	Formula
D1:D10	D1	=IF($A$1:$A$10= "Good", "OK",$A$1:$A$10)
E1:E10	E1	=IF(AND($A$1:$A$10= "Definitely", $B$1:$B$10= "Good"), "OK",$A$1:$A$10)
Dynamic array formulas.

 

[StephenCrump]

Try:

With Range(Cells(1, 1), Cells(10, 1))
    .Value = Evaluate("IF((" & .Address & "= ""Definitely""" _
            & ")*(" & .Offset(, 1).Address & "= ""Good""), ""OK""," & .Address & ")")
End With

(If you use AND, it will collapse the vector to a single value)

 

[My Aswer Is This]

Now that I see the comments way far to the right which I did not originally see.
It says this:
If cell in Column A range = 'Good' then change Column A
' ' cell to 'OK' Else leave the value as is

When I try it does not work.

 

[johnnyL]

Try:

With Range(Cells(1, 1), Cells(10, 1))
    .Value = Evaluate("IF((" & .Address & "= ""Definitely""" _
            & ")*(" & .Offset(, 1).Address & "= ""Good""), ""OK""," & .Address & ")")
End With

(If you use AND, it will collapse the vector to a single value)

Thank you so much @StephenCrump! That works!

 

[My Aswer Is This]

Try:

With Range(Cells(1, 1), Cells(10, 1))
    .Value = Evaluate("IF((" & .Address & "= ""Definitely""" _
            & ")*(" & .Offset(, 1).Address & "= ""Good""), ""OK""," & .Address & ")")
End With

(If you use AND, it will collapse the vector to a single value)

Steve. Can you help me to learn Evaluate
It appears to me the script looks for the Value "Good" in Range("A1:A10")
If it finds "Good" it changes the value to "OK"

But when I run your script or the other script it does not work.

 

[johnnyL]

@My Aswer Is This Let me repost all the working Data/code for you.

Book1
	A	B	C
1	Definitely	Good
2	Definitely	Good
3	Maybe	Bad
4	Maybe	Good
5	Maybe	Bad
6	Definitely	Good
7	Definitely	BAD
8	Maybe	BAD
9	Definitely	Good
10	Definitely	Good
11
Sheet1

Sub TestEvaluateIFV2()
'
' Check values in B1:B10
'
    With Range(Cells(1, 2), Cells(10, 2))
        .Value = Evaluate("IF(" & .Address & "= ""Good"", ""OK""," & .Address & ")")        '   If cell in Column B range = 'Good' then change Column B
'                                                                                           '           cell to 'OK' Else leave the value as is
    End With
End Sub

Sub TestEvaluateIF_AND_V2()
'
' Check values in A1:B10    AND needs to be replaced with '*' ;)
'
    With Range(Cells(1, 1), Cells(10, 1))
        .Value = Evaluate("IF((" & .Address & "= ""Definitely""" & ")*(" & _
                .Offset(, 1).Address & "= ""Good""), ""OK""," & .Address & ")")             '   If cell in Column A range = 'Definitely' & cell
'                                                                                           '           in Column B range = 'Good' then change
'                                                                                           '           Column A cell to 'OK' Else leave the value as is
    End With
End Sub

Let us know if those codes don't work for you.