Macro Fill Down Formula In Dynamic Table

[mellis]

I have the below macro that applies text to row working fine. However the line in red is not working. I essentially want to fill down the formula instead of the values, as in all of the other columns.

Any help would be appreciated!

Sub Workbook_Open()

ActiveWorkbook.RefreshAll
DoEvents

i = 2 'If you have headers i = 2
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Do Until i > LastRow
mySplit = Split(Trim(Range("A" & i).Value), " ")
If UBound(mySplit) > 0 Then
Range("A" & i).Value = mySplit(0)
bool = True
x = i
For Each Item In mySplit
If bool = False Then
Rows(i + 1).Insert
Range("A" & i + 1).Value = Item
Range("B" & i + 1).Value = Range("B" & x).Value
Range("C" & i + 1).Value = Range("C" & x).Value
Range("D" & i + 1).Value = Range("D" & x).Value
Range("E" & i + 1).Value = Range("E" & x).Value
Range("I" & i + 1).Value = Range("I" & x).Value
Range("J" & i + 1).Value = Range("J" & x).Value
Range("K" & i + 1).Value = Range("K" & x).Value
Range("L" & i + 1).Value = Range("L" & x).Value
Range("M" & i + 1).Value = Range("M" & x).Value
Range("N" & i + 1).Value = Range("N" & x).Value
Range("O" & i + 1).Value = Range("O" & x).Value
Range("P" & i + 1).Value = Range("P" & x).Value
Range("Q2:Q" & LastRow).Formula = "=IF(LEFT(A2,1)="N","NZ"&P2,"AU"&A2)"
i = i + 1
End If
bool = False
Next Item
i = i - 1
End If
i = i + 1
Loop
End Sub

 

[MARK858]

You need to double your quotes...

Range("Q2:Q" & LastRow).Formula = "=IF(LEFT(A2,1)=""N"",""NZ""&P2,""AU""&A2)"