Excel VBA IF statement

[realtoast]

Experts,

I have a data table, to which I need to insert a column B, then test whether data in cell D2 (Relationship) is "Employee". If so, in B2, add "P". If D2 is not Employee, leave blank. If I were doing this in formula, =IF([@[ Relationship]]="Employee","P","") works. But I need to do this within a series of macros.

The below script is where I'm starting, but I'm not getting the if statement. I first insert the column, B, then title the header, Participant_ID. Here, I currently just put "P" all the way down. I need to instead test if Relationship is "Employee", and if so, place "P". If not, leave blank.

Sub AA2_PID()
'
' This script inserts a column B, then tests whether data in cell E2 is "Employee" or not, if so
' then place "P" in cell B2, then drag down to all cells in column B. If not Employee, leave blank

Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("StartTable[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "Participant_ID"
Range("B2").Select
ActiveCell.FormulaR1C1 = "P"
Selection.AutoFill Destination:=Range("StartTable[Participant_ID]")

End Sub

Employee Code	Participant_ID	Name	Relationship
A5A0	P	Smith, BRIAN JEFFERY	Employee
A5A0		Smith, Kari	Spouse
A5A0		Smith, Timothy	Child
A5A0		Smith, Sara	Child
A5A0		Smith, Jerry	Child
A5A0		Smith, Kimberly	Child
A1HD	P	Jones, Stepanie	Employee
A58K		Jefferson, JASMIN MARISELA	Spouse
A55X		Jefferson, MICHELLE ORTEGA	Child
A59X		Jefferson, RAUL ALEJANDRO	Child

 

[DanteAmor]

Try this_

Sub Macro1()
  Range("StartTable[[#Headers],[Name]]").ListObject.ListColumns.Add Position:=2
  Range("B1").Value = "Participant_ID"
  Range("B2").FormulaR1C1 = "=IF([@Relationship]=""Employee"",""P"","""")"
End Sub

 

[MARK858]

Deleted as doesn't cover the OP's request to insert column (+ DanteAmor's code is cleaner)

 

[realtoast]

Try this_

Sub Macro1()
  Range("StartTable[[#Headers],[Name]]").ListObject.ListColumns.Add Position:=2
  Range("B1").Value = "Participant_ID"
  Range("B2").FormulaR1C1 = "=IF([@Relationship]=""Employee"",""P"","""")"
End Sub

Thank you. I was not able to get this script to work, but it lead me to recording a macro that almost resulted in the same script.

 

[DanteAmor]

I was not able to get this script to work

Try:

Sub Macro1()
  Range("B1").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
  Range("B1").Value = "Participant_ID"
  Range("B2").FormulaR1C1 = "=IF([@Relationship]=""Employee"",""P"","""")"
End Sub