Forum Discussion

Doris1785's avatar
Doris1785
Copper Contributor
Jun 06, 2022
Solved

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

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 ...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 07, 2022

    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
HansVogelaar's avatar
HansVogelaar
MVP
Jun 07, 2022

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

Resources