Fiquei na dúvida se pretende inserir a fórmula ou se já poderia ser o resultado direto.

Se puder ser o resultado direto (sem tratamento de erro, portanto, supondo que todas as procuras terão retorno):

Sub Macro1()

i = 10
j = Range("A1048576").End(xlUp).Row

Do While i <= j

Cells(i, 15) = Application.WorksheetFunction.VLookup(Range("M" & i), Sheets("sub").Range("B:D"), 3, 0)
Cells(i, 16) = Application.WorksheetFunction.VLookup(Range("O" & i), Sheets("sub").Range("B:D"), 3, 0)

i = i + 1

Loop

End Sub

Edson, testei o seu código não sei porque quando eu acrescento mais linhas ela somente funciona bem até a linha 20.

Em relação a sua duvida, o resultado pode ser direto.

frsnando

Não é para acontecer isso.

A referência para determinar a última linha é a coluna A.
Certifique-se de quando acrescentar dados, a coluna A nunca fique vazia.

Se não for este o caso, precisamos alterar a coluna de referência ou utilizar outro comando.

Funcionou perfeitamente.

obrigado.

Boa noite!!

Edson se me permite tenho também uma outra forma...

Sub AleVBA_Procv()
    Dim rng As Range
    Dim ws As Worksheet
     
    Set ws = Sheets("CONSULTA")
    Set rng = ws.Range("N9000").End(xlUp).Offset(, 14)
     
    ws.Range("O10").Resize(rng.Row - 2 + 1, 1).Formula = "=IF(N10="""","""",VLOOKUP(M10,sub!B:D,3,0))"
    ws.Range("P10").Resize(rng.Row - 2 + 1, 1).Formula = "=IF(N10="""","""",VLOOKUP(O10,sub!B:D,3,0))"
        
    Range("O:P").Copy
    Range("O:P").PasteSpecial Paste:=xlPasteValues
    
    Application.CutCopyMode = False
End Sub

Baixe o arquivo em:
http://www.sendspace.com/file/06wk71

Att