Macro code needed for combination of (If,Countif,vlookup)

[dharmadme]

I am creating multiple macro function workbook. Below formula is required for full fill my macro.
Please provide Macro VB code for below Excel formula

This formula is populate in sheet name "Worksheet 1" vlookup with "Worksheet 2"

=IF(COUNTIF(A1:$A$5000,A1)=1,VLOOKUP(A1,'Worksheet 2'!$A$1:$J$5000,10,0),"")

 

[RickXL]

I was not sure from your question whether you wanted a macro to carry out the functionality in your formula or whether you wanted the formula to be copied down using a macro. If it is the latter then I have a solution.

If you want the former then I should point out that a macro solution wll generally be slower than a worksheet formula solution.

Sub InsertFormula()
    Dim ws As Worksheet
    Set ws = Sheet1
    
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim myRng As Range
     
    With ws
        Set my_range = .Range("C1:C" & LastRow)
        myForm = "=IF(COUNTIF(A1:$A$" & LastRow & ",A1)=1,VLOOKUP(A1,Sheet2!$A$1:$J$5000,10,0),"""")"
        my_range.Formula = myForm
    End With
End Sub

If you want something else please add more information.

 

[dharmadme]

Thank you...