Insert formula to a cell using VBA and autofill formula to the cells below

[Doris1785]

Hi everyone,

I want to use VBA code to insert the formula ""=Index(Source!$J:$J, MATCH(1,($C5=Source!$C:$C)*($D5=Source!$D:$D),0))" into cell J5 in a destination worksheet named "Dest". The lookup data is in worksheet named "Source"

This formula returns the result when I tested it in Excel. However, the formula returns "#N/A" when run in VBA because the output formula added "@" in front of the lookup data sheet name "Source" as given below:

=INDEX(Source!$J:$J, MATCH(1,($C5=@Source!$C:$C)*($D5=@Source!$D:$D),0))

Can anyone advise how to fix the problem/issue?

My VBA codes are as follows:

Dim lastrow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Source")
With ThisWorkbook.Worksheets("Dest")

     lastrow = .Cells(Rows.Count, 2).End(xlUp).row

     .Range("J5").Formula = "=Index(Source!$J:$J, MATCH(1,($C5=Source!$C:$C)* _      ($D5=Source!$D:$D),0))"

      .Range("J5").AutoFill Range("J5:J" & lastrow)

End With

[#2 · Accepted solution]

@Doris1785 

Try this:

Sub Test()
    Dim therow As Long
    Dim lastrow As Long
    Application.ScreenUpdating = False
    With ThisWorkbook.Worksheets("Dest")
        lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row
        For therow = 5 To lastrow
            .Cells(therow, 10).Value = Evaluate( _
                "=INDEX(Source!$J:$J, MATCH(1,(Source!$C:$C=$C" & _
                therow & ")*(Source!$D:$D=$D" & therow & "),0))")
        Next therow
    End With
    Application.ScreenUpdating = True
End Sub

[#3]

Hi Hans,
I appreciated your reply and thank you so much for providing the solution which I have tested and it works. I learned something new (the EVALUATE function) from you.