VBA Apply Formula if Certain Criteria is Met

[rsmeyerson]

Hello. I'm looking for some help. I would like to apply a formula to a specific sheet, column "R" if a specific name appears in column "B". I have tried the code below, but it is inserting the formula into column "R" for all rows, regardless of name.

With Sheets("RetailBack")      
     Dim a1 As Long, lstrw As Long
      lstrw = .Range("A" & Rows.Count).End(xlUp).Row
      For a1 = 2 To lstrw
           If .Range("B" & a1).Value = "NAME1" Or .Range("B" & a1).Value = "NAME2" Then
               .Range("R2:R" & .Cells(.Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "XX"
           End If

Your help is appreciated.

 

[vcoolio]

Hello RSM,

How would you like this to work?

Do you mean filter on:-

Name1 OR Name2 in Column B AND "New" in Column E before placing the formula in Column R?

Could you clarify this please.

Cheerio,
vcoolio.

 

[rsmeyerson]

Sorry I wasn't more clear. I would like to place the formula after filtering NAME1 OR NAME2 in column B AND "New" in column E.

Thank you,
Bob

 

[vcoolio]

Hello RSM,

Hopefully I've got it right for you using the following code:-

Sub Test()

        Dim ws As Worksheet: Set ws = Sheets("RetailBack")
        Dim lr As Long: lr = ws.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

With ws.Range("A1:Q" & lr)
       .AutoFilter 2, "NAME1", xlOr, "NAME2"
       .AutoFilter 5, "NEW"
       Range("R2:R" & lr).Formula = "XX"
       .AutoFilter
End With
       
Application.ScreenUpdating = True

End Sub

Below is the link to a little sample file showing you how this works:-

http://ge.tt/5uElZXu2

Click on "GO" to see it work.

You'll note that the code only places "XX" in Column R if NAME1 or NAME2 in Column B has the value "NEW" on the same row in Column E.

If its OK then please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[rsmeyerson]

Perfect! Exactly what I needed. Thanks so much for your help.

Bob

 

[vcoolio]

You're welcome Bob.
I'm glad that I was able to help you again.

Cheerio,
vcoolio.