VBA needing to automatically fill a column with an if statement when column locations are not fixed

I am trying to do the following:

Find a column called Employee SGrp, then add a column to the right of Employee SGrp which has the heading of Employee Classification as the first column. I then auto fill this column with a formula.

However the data is based on a report and the column location are not fixed (i.e. Employee SGrp may be in M one month and L the next). Therefore I can't use cell references like 'A2' in my formulas. In addition there are a few different options in the previous two columns so I am going to need to use an if statement as my formula to fill the columns. For example if "driver" write contractor, if part time write "permanent" if casual write "permanent".

I have added a column using the offset and resize features and have added add a value to serve as the heading. I have managed to put a formula in but only have it fill basic calculation such as sum or fill in a number) I have not been able to nest an if statement in this form and have also been unable to build the if statement properly. I can create a formula using fixed values (e.g. A2) but I need to be able to reference the column by name not fixed location since it isn't always in the same cell reference. I would appreciate any help in accomplishing this as I haven't found anything yet which will allow me to accomplish what I need to do.

'Insert a new column to the right of rngLocation
rngEmpClass.Offset(, 1).EntireColumn.Insert
Set rngNewCol = rngEmpClass.Offset(, 1)


rngNewCol.Value = "Employee Classifications"
With rngNewCol.Offset(1).Resize(lRow)
    .NumberFormat = "General"   'Set column to General format
    .Formula = "2"