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.

 

[johnnyL]

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.

Actually the 'Good' is checked in Column B. There is an offset(, 1) in the 2nd script (If(AND)) Which makes it column B. Please read the comments in my latest post.

 

[johnnyL]

Maybe these 2 versions of code ... translated from Cells to just Range makes more sense:

Sub TestEvaluateIFV2A()
'
' Check values in B1:B10
'
    With Range("B1:B10")
        .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_V2A()
'
' Check values in A1:B10    AND needs to be replaced with '*' ;)
'
    With Range("A1:A10")
        .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

 

[My Aswer Is This]

@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.

The first one worked but not the second one. Thanks

 

[johnnyL]

What do you mean when you say second one didn't work? Are you saying column A didn't change at all, it erased all of column A?
Didn't work doesn't really describe what happened or didn't happen.

 

[My Aswer Is This]

OK This script worked:

Sub TestEvaluateIFV2A()
'
' Check values in B1:B10
'
    With Range("B1:B10")
        .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

 

[johnnyL]

That is the same as the other one you said worked, Only change was from Range(Cells to just Range.

 

[My Aswer Is This]

That is the same as the other one you said worked, Only change was from Range(Cells to just Range.

I will get back with you in a minute or two and show you which one did not work

 

[My Aswer Is This]

OK this script worked:

Sub TestEvaluateIF_AND_V2A()
'
' Check values in A1:B10    AND needs to be replaced with '*' ;)
'
    With Range("A1:A10")
        .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

I now see we are looking at column A and B
A Must have "Definitely"
And B must Have "Good"

I did not do that first time:
Thanks for all your help

 

[johnnyL]

Yes. The IF(AND script looks at Columns A & B and changes Column A when conditions are met.
The IF script looks at Column B & changes Column B when condition is met.

 

[My Aswer Is This]

Yes. The IF(AND script looks at Columns A & B and changes Column A when conditions are met.
The IF script looks at Column B & changes Column B when condition is met.

Thanks I learned one new thing here today.
Never have used Evaluate before today
Take care