If And Statement with Variable Range

[sushi514]

Hi All,

I think I'm having issues with my variable range when I try to use the below. I'm just needing to force a "Yes" value in any cells in Columns D3 - E3 (going down all rows that can vary each time) if they are blank, but only if the corresponding cell in Column F reads "Yes" as well:

If Range("D3:E" & Range("A" & Rows.Count).End(xlUp).Row).Value = "" And Range("F3:F" & Range("A" & Rows.Count).End(xlUp).Row).Value = "Yes" Then
Range("D3:E").Value = "Yes"
End If

Any suggestions?

 

[Fluff]

How about

Sub sushi()
   With Range("D3:E" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("if((@="""")*(" & .Offset(, 2).Resize(, 1).Address & "=""Yes""),""Yes"",@)", "@", .Address))
   End With
End Sub

 

[sushi514]

Thank you! I realized what I needed was the opposite; to fill the blanks in Columns D3 - E with "Yes" only if F was blank, so I changed it to this:

Sub testf2()

With Range("D3:E" & Range("A" & Rows.Count).End(xlUp).Row)
.Value = Evaluate(Replace("if((@="""")*(" & .Offset(, 2).Resize(, 1).Address & "=""""),""Yes"",@)", "@", .Address))
End With
End Sub

But now I keep getting zero values for where Column F reads "Yes"

 

[Fluff]

Will columns D & E be blank, or can they have values you want to keep?

 

[sushi514]

For cells in columns D & E that already have values (in this case, "OK"), should remain as-is. Only where F is blank do I want to force "Yes" in Column D, where cells in D are blank. And where F is blank do I want to force "Yes" in Column E, where cells in E are blank. Basically if I were doing this manually, I filter on Column F for blanks, then force "Yes" wherever blank in D and E (expected values highlighted in attached).

 

[Fluff]

OK, try

Sub sushi()
   With Range("D3:E" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("if((@="""")*(" & .Offset(, 2).Resize(, 1).Address & "=""""),""Yes"",if(@="""","""",@))", "@", .Address))
   End With
End Sub

 

[sushi514]

You are awesome - thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.