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.

 

[Prish]

Change:

.Range("R2:R" & .Cells(.Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "XX"

to

.Range("R" & a1).FormulaR1C1 = "XX"

 

[vcoolio]

Hello Rmeyerson,

See if the following modification works for you (untested):-

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("B" & a1).Offset(, 16).FormulaR1C1 = "XX"
                   End If
               Next
       End With

I hope that this helps.

Cheerio,
vcoolio.

 

[rsmeyerson]

Thanks for your reply. My formula is only being placed in cell "R2" even though the specified name is present in cells other than "B2".

 

[Prish]

Does column A have any blanks?

 

[Prish]

Try this:

With Sheets("RetailBack")
Dim a1 As Long, lstrw As Long
      lstrw = .Range("[COLOR=#ff0000]B[/COLOR]" & Rows.Count).End(xlUp).Row
      For a1 = 2 To lstrw
           If .Range("B" & a1).Value = "NAME1" Or .Range("B" & a1).Value = "NAME2" Then
[COLOR=#ff0000]           .Range("R" & a1).FormulaR1C1 = "XX"[/COLOR]
           End If
Next
End With

 

[vcoolio]

Hello RSMeyerson,

Perhaps changing the method may be a better idea:-

Sub Test()

Dim ws As Worksheet: Set ws = Sheets("RetailBack")
 
Application.ScreenUpdating = False

With ws.Range("B1:B" & ws.Range("B" & ws.Rows.Count).End(xlUp).Row - 1)
        .AutoFilter 1, "NAME1", xlOr, "NAME2"
        .Offset(1, 16).Formula = "XX"
        .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

Cheerio,
vcoolio.

 

[rsmeyerson]

vcoolio,

Your AutoFilter solution worked perfectly. Thanks.

 

[vcoolio]

You're welcome RSM.
Good to know that it now works for you and I'm glad to have been able to help out.

Cheerio,
vcoolio.

 

[rsmeyerson]

Can you please help with adding a 2nd filter to this AutoFilter formula? In addition to filtering column "B", I would also like to filter column "E" with criteria "NEW".