Code to write phrase for entire column based on keyword in different column

[AlwaysLearning2018]

Hi All,

Have a question as to how I can go about creating a phrase in 1 column based on a keyword in a different column. For example, if a number in the range of 500-750 Appears in column C, I need “do not instruct” in column Obut only on those rows in which the range is >= 500 and <=750. I already have a code that is similar, inwhich I have a keyword, say “futures” in column F and the output in column Oshows “variation” and it works very well. That code also is written so that “0s” don’t appear in othercolumns. I thought I would be able totweak that code, but I am not having success with trying.
Any help would be greatly appreciated.
Thanks!!

 

[AlwaysLearning2018]

The below formula is what someone here helped me with and it works perfectly. My attempts at tweaking for a different column (and numbers) have been unsuccessful.

With Range("F2", Range("C" & Rows.Count).End(xlUp).Offset(, 3))
.Value = Evaluate(Replace("If(@=""Variation Margin"",""Futures Margin""," & .Address & ")", "@", .Offset(, -3).Address))
.Value = Evaluate(Replace("If(@=""Repo"",""Repo Margin""," & .Address & ")", "@", .Offset(, -3).Address))
.Value = Evaluate(Replace("If(@=0,"""",@)", "@", .Address))
End With

 

[jbenfleming]

I'm a tad confused on your problem, but it seems you could copy down the following formula in column O:

=IF(AND(C1>=500,C1<=750),"do not instruct","")

if you insist on VBA (I don't think it is necessary):

Sub num_check()
Dim nums As Long, num As Long
nums = Range("c1").End(xlDown).Row
For num = 1 To nums
    If Range("c1").Offset(num - 1, 0).Value >= 500 And Range("c1").Offset(num - 1, 0).Value <= 750 Then
        Range("c1").Offset(num - 1, 12).Value = "do not instruct"
    End If
Next num
End Sub

 

[AlwaysLearning2018]

Thank you! So helpful. Also, I need to add 1 more condition. In addition to that number range, I also need the condition met that column L reads "pending". Right now obviously the phrase "do not instruct" appears any time the number range criteria is met. I need the code to add "do not instruct" if the number range is met AND column L = pending. Any guidance would be greatly appreciated.

Thank you!!!

 

[jbenfleming]

Formula:

=IF(AND(C1>=500,C1<=750,L1="pending"),"do not instruct","")

Code:

Sub num_check()
Dim nums As Long, num As Long
nums = Range("c1").End(xlDown).Row
For num = 1 To nums
    If Range("c1").Offset(num - 1, 0).Value >= 500 _
    And Range("c1").Offset(num - 1, 0).Value <= 750 _
    And Range("c1").Offset(num - 1, 9).Value = "pending" Then
        Range("c1").Offset(num - 1, 12).Value = "do not instruct"
    End If
Next num
End Sub

 

[Fluff]

How about

With Range("O2", Range("C" & Rows.Count).End(xlUp).Offset(, 12))
   .Value = Evaluate(Replace(Replace("If((@>=500)*(@<=700)*(@l=""Pending""),""do not instruct""," & .Address & ")", "@l", .Offset(, -3).Address), "@", .Offset(, -12).Address))
   .Value = Evaluate(Replace("If(@=0,"""",@)", "@", .Address))
End With

 

[AlwaysLearning2018]

Hi all,

Everything worked perfectly. Thank your so much. The last hurdles in trying to conquer are if any row in column J that = ATO then I need every row in column O to populate "Automated". I tried to apply what has already been provided to me but can't seem to get anything to work. Also if any row in column J = PRD and any row in column L = "waiting" then I need every row in column O that satisfies both criteria to = "Manual".

As always, any guidance anyone can give would be greatly appreciated. Happy Holidays everyone!!

 

[AlwaysLearning2018]

Hi all,

Sorry to ask again but any ideas? Thank you for all the help!!!