Define formula in a code

[RAJESH1960]

Hello Experts
I am trying to add an extra column in the Edited Portal sheet within JohnnyL's code. I have edited and made some changes successfully. I want to define the formula in the code and get the result under Narration column N. The formula to be written in the code is shared below. The test workbook link is also shared below

2. Step to add Narration.xlsm

=$C$1&" "&C2&" "&$D$1&" "&D2&" "&$E$1&" "&E2&" "&$F$1&" "&TEXT(F2,"dd-mm-yyyy")&" "&$L$1&" "&L2&" "&$M$1&TEXT(M2,"dd-mm-yyyy")

 

[johnnyL]

Replace the following code:

    wsDestination.Columns("F:F").NumberFormat = "@"                             ' Set column to text format to prevent excel changing dates
    wsDestination.Range("A2").Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray  ' Display results to DestinationSheet

with:

    wsDestination.Columns("F:F").NumberFormat = "@"                             ' Set column to text format to prevent excel changing dates
    wsDestination.Range("A2").Resize(UBound(OutputArray, 1), UBound(OutputArray, 2)) = OutputArray  ' Display results to DestinationSheet
'
    Dim DestinationLastRow  As Long
'
    DestinationLastRow = wsDestination.Range("A" & wsDestination.Rows.Count).End(xlUp).Row  ' Get last row used in column A of the destination sheeet
'
    wsDestination.Range("N2:N" & DestinationLastRow).Formula = "=$C$1 & "" "" & C2" & _
            " & ""  "" & $D$1 & "" "" & D2 & ""  "" & $E$1 & "" "" & E2" & _
            " & ""  "" & $F$1 & "" "" & TEXT(F2,""dd-mm-yyyy"") & ""  "" & $L$1" & _
            " & "" "" & L2 & ""  "" & $M$1 & "" "" & TEXT(M2,""DD-MM-YYYY"")"   ' Copy Narration Formula to Column N

 

[RAJESH1960]

JohnnyL. Can you please convert the result to value in place of formulas.? I mean copy, pastespecial, values....
because I would be shifting the required rows with narration to a different sheet and the formula will show an error.

 

[johnnyL]

Add the following to the end of my last posted code:

'
    wsDestination.Range("N2:N" & DestinationLastRow).Copy                       ' Copy formula range into memory
    wsDestination.Range("N2:N" & DestinationLastRow).PasteSpecial xlPasteValues ' Paste just the vales back to range
    Application.CutCopyMode = False                                             ' Clear clipboard & 'marching ants' around copied range

 

[RAJESH1960]

Just like I imagined it. Thanks JohnnyL.

 

[johnnyL]

Glad to help.