If "text" then "formula" in another cell VBA

[cmschmitz24]

Hello,

I need help with an if then vba -

I have either WRSTEA or WRSEXC in column F, I need to have the corresponding formula populate in column R or S.

Example:
If F2 = WRSTEA, then R2 = Blank, S2 = (O2*0.0295)
If F5 = WRSEXC, then R2 = (O5*0.081), S2 = Blank

Thanks!

 

[Scott Huish]

I assumed that your second example, if F5 was WRSEXC that you were modifying R5 and S5, not R2 and S2.

Sub test()
Dim c As Range
For Each c In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
Select Case UCase(c)
    Case "WRSTEA": Cells(c.Row, "R") = "": Cells(c.Row, "S").Formula = "=" & Cells(c.Row, "O").Address(0, 0) & "*0.0295"
    Case "WRSEXC": Cells(c.Row, "S") = "": Cells(c.Row, "R").Formula = "=" & Cells(c.Row, "O").Address(0, 0) & "*0.081"
    Case Else: c.Offset(, 12).Resize(, 2).ClearContents
End Select
Next
End Sub

 

[cmschmitz24]

I assumed that your second example, if F5 was WRSEXC that you were modifying R5 and S5, not R2 and S2.

Sub test()
Dim c As Range
For Each c In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
Select Case UCase(c)
    Case "WRSTEA": Cells(c.Row, "R") = "": Cells(c.Row, "S").Formula = "=" & Cells(c.Row, "O").Address(0, 0) & "*0.0295"
    Case "WRSEXC": Cells(c.Row, "S") = "": Cells(c.Row, "R").Formula = "=" & Cells(c.Row, "O").Address(0, 0) & "*0.081"
    Case Else: c.Offset(, 12).Resize(, 2).ClearContents
End Select
Next
End Sub

Scott, this works perfectly. Thanks so much!
And yes you were correct to assume the corresponding cells for the 2nd formula.