Please see attached workbook.
My goal is to set column A to "YES" if criteria is met in Column B and Column C. I am trying to accomplish this without looping. I was provided the code listed under macro "test" which works well, but only lets me set Column A based on Criteria in Column B. I am now trying to expand it so that it uses criteria from Column B and Column C.
The code that works, setting Column A to yes if "myCriteria" value exists in Column B
Sub test()
Dim myField As Long, myCriteria
myCriteria = Range("h2").Value
If Not IsNumeric(myCriteria) Then myCriteria = Chr(34) & myCriteria & Chr(34)
With Cells(1).CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1).Columns(1)
.Columns(1).Value = Evaluate("if(" & .Columns(2).Address & "=" & myCriteria & ",""Yes""," & .Address & ")")
End With
End With
End Sub
The code that doesn't work -- attempting to expand the criteria necessary to set Column A to "YES" to include a numeric set in range G2.
Sub test2()
Dim myField As Long, myCriteria
myField = Range("g2").Value
myCriteria = Range("h2").Value
If Not IsNumeric(myCriteria) Then myCriteria = Chr(34) & myCriteria & Chr(34)
With Cells(1).CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1).Columns(1)
.Columns(1).Value = Evaluate("if(and(" & .Columns(3).Address & "=" & myField & "," & .Columns(2).Address & "=" & myCriteria & "),""Yes""," & .Address & ")")
End With
End With
End Sub
AutoFilterSample.xlsm
Bookmarks