VBA Excel ?#NAME# error after insert function

0
votes

After insert a function (SUMIF) with VBA in excel, I got a name error in those cells. However if I click and enter into a one of those cells, error is gone and functions is calculated correctly.

Does anyone know how to fix that?

Thanks in advance.

This is the VBA code:

Do While Not rst.EOF
With rst
    Sheets("Test").Cells(linea, 1) = .Fields("Posting Date")
    Sheets("Test").Cells(linea, 2) = .Fields("Entry No_")
    Sheets("Test").Cells(linea, 3) = .Fields("G_L Account No_")
    Sheets("Test").Cells(linea, 4) = .Fields("Document No_")
    Sheets("Test").Cells(linea, 5) = .Fields("External Document No_")
    Sheets("Test").Cells(linea, 6) = "=customFunction(E" & line & ")"
    Sheets("Test").Cells(linea, 7) = .Fields("Description")
    Sheets("Test").Cells(linea, 8) = .Fields("Debit Amount")
    Sheets("Test").Cells(linea, 9) = .Fields("Credit Amount")
    Sheets("Test").Cells(linea, 10) = .Fields("Amount")
    Sheets("Test").Range("K" & linea).Formula = "=SUMIF(F:F,F:F,J:J)"

    line = line + 1
    rst.MoveNext
End With
Loop
2
excelvbafunction
Why are using linea for the row number but incrementing line = line + 1 ? - user4039065
Typing error. That is line instead - user3472675
You do know you can populate a range without looping right? For example put 1 in range A1:A10 and make B1 = A1 then run this in the debug window (ctrl-g in the VBE) Range("B2:B10").Formula = "=B1+A2" It auto increments without needed to loop through rows 2 to 10 - Dan Donoghue

2 Answers

0
votes

You should probably add:

Calculate

at the end of your VBA function to cause Excel to update the, err, calculations

0
votes

I found the solution. I was getting #name? error because I was using local function name. To solve this I used .FunctionLocal, this "translates" the Excel functions names if you are using a different language.

Thanks anyway.