Guru do Excel e Power BI

Estou a construir um codigo como podem ver na folha abaixo.
ultlinha = Range("h100000").End(xlUp).Row


For linha = 2 To ultlinha
pvp = Cells(linha, 8)
If pvp = "preço livre" Then
apagalinha = Cells(linha, 8).Row
Rows(apagalinha).ClearContents


ElseIf Cells(linha, 8).Value <> "" Then

Cells(linha, 11) = calculapva(Cells(linha, 8))
Cells(linha, 12) = escalao(Cells(linha, 8))

ElseIf Cells(linha, 10).Value <> "" Then

cnp = Sheets(1).Cells(linha, 1).Value
Sheets(2).Activate
pvpmax = WorksheetFunction.VLookup(cnp, Range("A1:B287"), 2, 0)
Sheets(1).Activate
Cells(linha, 9) = pvpmax
Cells(linha, 11) = pvanot(Cells(linha, 9), Cells(linha, 10))

End If

Next

no entanto correndo a formula passo a passo no F8, o valor da variavel pvpmax fica sempre iguar a zero, para testar copiei para outro modulo, apenas a formula do vlookup ( Modulo 2) e lá a formula devolve o valor correcto.
O que se passa?

#
O problema ocorre porque na coluna A da planilha PVPMAX os valores são numéricos, porém na coluna A da planilha BD Infarmed os valores são textos que se parecem com valores numéricos.

Para confirmar coloque em qualquer célula vazia em ambas as planilhas ~~~> =ÉNÚM(A2) arraste algumas células para baixo e verifique os resultados.

Por outro lado, no Módulo2 o resultado é o desejado porque você colocou o valor procurado no próprio código e é numérico, no entanto se no lugar de ~~~> cnp = 5313556 #você colocar ~~~> cnp = Sheets("BD Infarmed").[A11741] irá resultar erro.

O caminho mais simples para contornar seria acrescentar zero ao valor procurado para torná-lo numérico, conforme em vermelho abaixo:
cnp = Sheets(1).Cells(linha, 1).Value + 0