VBA help - Auto populate phrase based on conditions

[AlwaysLearning2018]

Hello all,

I am trying to automate a report that is run daily so I think I have to use VBA so it does not have to be touched. What I'm trying to figure out to do is for each instance in a row where I see "SA" in column J, I need the phrase "Load" to populate in every row in column O. Also if any row in column J = SWP and any row in column N = "invalid" then I need every row in column O that satisfies both criteria to = "Sweep".

Any ideas anyone can give would be greatly appreciated!!

Regards

 

[ClaireS]

Try this formula in column O (assuming your data is in row 2):
=IF(J2="SA","Load",IF(AND(J2="SWP",N2="Invalid"),"Sweep",""))

 

[JLGWhiz]

Here is a vba solution if that is what you need

Sub t()
Dim sh As Worksheet, c As Range
Set sh = ActiveSheet
    With sh
        .UsedRange.AutoFilter 10, "SA"
        Intersect(.UsedRange.Offset(1), .Columns("O")).SpecialCells(xlCellTypeVisible) = "Load"
        .AutoFilterMode = False
        .UsedRange.AutoFilter 10, "SWP"
        For Each c In Intersect(.UsedRange.Offset(1), .Columns("N")).SpecialCells(xlCellTypeVisible)
            If LCase(c.Value) = "invalid" Then c.Offset(, 1) = "Sweep"
        Next
        .AutoFilterMode = False
    End With
End Sub

 

[AlwaysLearning2018]

Thank you both! I am not having luck with the second part of the vba code. And apologies, I am looking for column O to = "Sweep" if column J = SWP AND Column N = OK. The "OK" piece is in all caps so not sure if the lcase function matters. Any help would be very much appreciated!!!

 

[Fluff]

How about

Sub AlwaysLearning2018()
   With Range("A1").CurrentRegion
      .AutoFilter 10, "SA"
      .AutoFilter 14, "OK"
      .Columns(15).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Load"
      .AutoFilter 10, "SWP"
      .AutoFilter 14, "Invalid"
      .Columns(15).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Sweep"
      .Parent.AutoFilterMode = False
   End With
End Sub

 

[AlwaysLearning2018]

Worked perfectly. You are all AMAZING. thank you so much!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[AlwaysLearning2018]

Hi Fluff,

Using this same coding, is there a way to exclude values in other columns? For example, if column K = "183" but column J does NOT equal SWP or SA I want the phrase "Not Instructed" to appear in every row of column O where these conditions are met. Thank you!!!!

 

[Fluff]

Try

Sub AlwaysLearning2018()
   With Range("A1").CurrentRegion
      .AutoFilter 10, "SA"
      .AutoFilter 14, "OK"
      .Columns(15).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Load"
      .AutoFilter 10, "SWP"
      .AutoFilter 14, "Invalid"
      .Columns(15).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Sweep"
      .AutoFilter 14
      .AutoFilter 10, "<>SWP", xlAnd, "<>SA"
      .AutoFilter 11, 183
      .Columns(15).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Not Instructed"
      .Parent.AutoFilterMode = False
   End With
End Sub

 

[AlwaysLearning2018]

As usual, worked like a charm. I cannot thank you enough for all of your help. So grateful!!