Need VBA code to fill header values and drag formula till end of last row on target column

[MicroTees]

Hi experts

i have set of data need to find brand on F:F values

if column values end with (Y) or (Y1) its NT-BDEALER
rest of all data Non NT-BDEALER

and last 3 columns(V,W,X) from U col,Need to fill with V= Date,W=Name,X=ISIN Code. and fill formula down till end of row.
V col

=TODAY()-5

W col

=VLOOKUP(J2,vlookup!A:B,2,0)

x col

=E2

find the attachment expected sheet is my requirement

[JohnTopley]

in G2

=IFERROR(IF(FIND("Y",F2),"NT-DELEAR"),"NOT NT-DEALER")

in W2

=IFERROR(VLOOKUP(J2,vlookup!A:B,2,0),"")

[MicroTees]

Brah Can u help me with VBA Code?

[Sintek]

Sub J3v16()
With Sheets("Master_Data").Cells(1).CurrentRegion.Resize(, 24)
    .Cells(1, 22).Resize(, 3) = Array("Date", "Name", "ISIN code")
    With .Offset(1).Resize(.Rows.Count - 1)
        .Columns(7).Formula = "=IFERROR(IF(FIND(""Y"",F2),""NT-DELEAR""),""NOT NT-DEALER"")"
        .Columns(22).Resize(, 3).Formula = Array("=TODAY()-5", "=IFERROR(VLOOKUP(J2,vlookup!A:B,2,0),"""")", "=E2")
    End With
    .Columns.AutoFit
End With
End Sub

[MicroTees]

Thanks Boss !!! Solved Thanks for your time

[Sintek]

I only took over where John left off...Glad to have contributed...Tx for rep +

[seercoven]

I just drop by and saw this. I've notice there's some typo and mismatch in john formula. Since most search feature are case sensitive, text like CAMPBELL SOUP COMPANY may not be detected by the search/find feature. Maybe you can try using this as formula.

=IF(OR(ISNUMBER(SEARCH("(Y)",F2)),ISNUMBER(SEARCH("(Y1)",F2))),"NT-BDEALER","NON NT-BDEALER")

Or this formula if you have a fixed search text range containing (Y) or (Y1) which in my case is K2:K3

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($K$2:$K$3,F2)))>0, "NT-BDEALER", "NON NT-BDEALER")

Final VBA code with thanks to sintek

Sub J3v16()
    With Sheets("Master_Data").Cells(1).CurrentRegion.Resize(, 24)
        .Cells(1, 22).Resize(, 3) = Array("Date", "Name", "ISIN code")
        With .Offset(1).Resize(.Rows.Count - 1)
            .Columns(7).Formula = "=IF(OR(ISNUMBER(SEARCH(""(Y)"",F2)),ISNUMBER(SEARCH(""(Y1)"",F2))),""NT-BDEALER"",""NON NT-BDEALER"")"
            .Columns(22).Resize(, 3).Formula = Array("=TODAY()-5", "=IFERROR(VLOOKUP(J2,vlookup!A:B,2,0),"""")", "=E2")
        End With
        .Columns.AutoFit
    End With
End Sub

[MicroTees]

Thanks Boss, great help!! Solved @Seecoven and @sintex.