Só pelas informações que colocou só da para orientar a utilizar a pesquisa no forum e procurar por PROCV ou VLOOKP que é o correspondente ao procv no VBA e tambem sobre Range Dinamico.

Vou tentar explicar melhor. Pretendo inserir a PROCV na célula H2 e quero que ao clicar no botão ele arraste essa PROCV até a ultima linha da planilha, só que no mês seguinte essa ultima linha vai variar então preciso que o código entenda aonde é a ultima linha. Minha PROCV está assim: VLOOKUP(RC[-3],COMPLETA!R1C1:R1485C2,2)

Pensei em um loop igual ao abaixo, que preenche enquanto a célula não for vazia, mas em vez de ele somar 10 ao valor como abaixo, preciso que ele aplique a PROCV na célula.

Dim i As Integer
i = 1

Do While Cells(i, 1).Value <> ""
Cells(i, 2).Value = Cells(i, 1).Value + 10
i = i + 1
Loop

Se convertir corretamente, veja se consegue ajustar :

Estamos inserindo a formula na Sheet2 efetuando a pesquisa na Sheet1

Sub Vlookup_AltoFill()

    Dim myRange As String
    Dim iniRow As Long
    Dim Ultimalinha As Long
    Dim procv_Por
    
    iniRow = 2 'Linha inicial para pesquisa
    Ultimalinha = Sheet1.Range("A" & Rows.Count).End(xlUp).Row 'Ultima linha coluna A

    'Definimos o Range para pesquisa
    myRange = "Sheet1!A$" & iniRow & ":C$" & Ultimalinha

    procv_Por = "E2" 'Valor Procurado na celula E3
    
    'Inserimos a formula
    Range("H2").Formula = "=VLOOKUP(" & procv_Por & "," & myRange & ",2)"

    'Preenchemos até a ultima linha
    Range("H2:H" & Ultimalinha).FillDown
    
End Sub

Perfeito, fiz os ajustes das referências e funcionou perfeitamente.

Bom que ainda deu pra eu entender a lógica.