Assign formula to a cell in VBA - Appears a @

Asked

Viewed 25 times

-3

I am learning VBA and in a sub where I try to assign the command below:

=PROCX([@[Empresa Emitente]];FORNECEDORES[Empresa Emitente];FORNECEDORES[DEPART];"";0;1)

Sub EXTRAIR_DEPART_UPDATE()

    Dim lastRowNoClear

    lastRowNoClear = Range("A16").End(xlDown).Row
    
    For i = 16 To lastRowNoClear
    
        Range("Q" & i).Select
        If Cells(i, 17) = "" Then
            Range("Q" & i).Value = "=PROCX([@[Empresa Emitente]],FORNECEDORES[Empresa Emitente],FORNECEDORES[DEPART],"""",0,1)"
        End If
        
    Next i
    
End Sub

Gives the following error #NOME? and I realized that VB puts a @ after the sign of =

Could you tell me what I’m doing wrong?

excel vba macro fórmula

  • Recommendation is not to leave everything in high box link.

1 answer

0


How to fix a #NAME?

In the specific question, I believe the error is in the declaration of the formula within the VBA, because it will not recognize the command PROCX in en.

Try:

Range("Q" & i).Value = "=XLOOKUP([@[Empresa Emitente]],FORNECEDORES[Empresa Emitente],FORNECEDORES[DEPART],"""",0,1)"

Instead of:

Range("Q" & i).Value = "=PROCX([@[Empresa Emitente]],FORNECEDORES[Empresa Emitente],FORNECEDORES[DEPART],"""",0,1)"

Browser other questions tagged excel vba macro fórmula

You are not signed in. Login or sign up in order to post.