I haven't used VBA for about 17 years, so I am beyond rusty!
I am trying to create a formula which puts into one column the forecast data for every month, apart from the current month which will have the actual data.
The data is an export from SAP, and it is very annoying in that the order of the columns is not always the same
So the formula would normally be like =if(d2="8 November",F2,G2) but I cannot be sure that the period will be in column D.
So I need some code that will identify the column which in row 1 contains the heading "Posting Period".
Attached is my attempt, which needless to say doesn't work!!
Code
Dim Per As String
Dim Mth
If Range("A2").Value = "Posting Period" Then
Per = "A2"
Else
If Range("B2").Value = "Posting Period" Then
Per = "B2"
Else
If Range("C2").Value = "Posting Period" Then
Per = "C2"
Else
If Range("D2").Value = "Posting Period" Then
Per = "D2"
End If
End If
End If
End If
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(left(Per,2)=Mth,RC[-1],RC[-2])"
Display More