Error 1004 FormulaLocal with SEERRO

Question

I am trying to use vba to insert a formula into cell on my Sheet. I believe it can be done with the formula I have below, but unfortunately I do not know how to fix my syntax below.

What's wrong with my FormulaLocal syntax?

The strange thing is that using R.Formula, it works, but the cell gets the error #NAME

   Sub InsertIFERROR()
        Dim R As Range
        Set R = Application.InputBox("Select a range", "Get Range", Type:=8)
        R.Select
        For Each R In Selection.SpecialCells(xlCellTypeFormulas)

            R.FormulaLocal = "=SEERRO(" & Mid(R.FormulaLocal, 2) & ",""-"")"

        Next R
    End Sub

If the error you see is indeed `#NAME`, your Excel might not be in Portuguese at which point you can't specify Portuguese function names in `FormulaLocal`. Why do you want to [use `FormulaLocal`](http://stackoverflow.com/a/33540940/11683) anyway? — GSerg, Mar 03 '17 at 16:02
`debug.print Mid(R.FormulaLocal, 2)` and see if it returns what you think it should — Scott Craner, Mar 03 '17 at 16:12
Once you solve the "language" issue you can avoid both the `Select` and the loop and simply write `R.SpecialCells(xlCellTypeFormulas).Formula = "=IFERROR(" & Mid(R.FormulaLocal, 2) & ",""-"")"` — user3598756, Mar 03 '17 at 16:13
But my Excel is in PT-BR. This is the strangest thing. All of my formulas in the Worksheet are in PT-BR. Error #NAME occurs when I use R.Formula, but if I access the cell formula and give an enter, the error disappears — G. Sena, Mar 03 '17 at 16:24
Doesn't PT-BR use `;` instead of `,` ? Check the original formula or your system's regional setting for the list separator. — , Mar 03 '17 at 16:30
@G.Sena That happens when you provide a local formula using `Formula`. user3598756 [meant](http://stackoverflow.com/questions/42583139/error-1004-formulalocal/42583295?noredirect=1#comment72299637_42583139) `R.SpecialCells(xlCellTypeFormulas).Formula = "=IFERROR(" & Mid(R.Formula, 2) & ",""-"")"`, but that will only work if all formulas in `R` are the same, otherwise you do need the loop with that logic inside. — GSerg, Mar 03 '17 at 16:31
Perhaps you actually have PT-PT, not PT-BR. In that case, use `SE.ERRO` and a `;` instead of `SEERRO` and `,`. — , Mar 03 '17 at 16:36
I thought that this could reasonably be closed as a typographical error that could not be reproduced then decided instead that the error may be more common than is immediately apparent. I've posted an answer below and edited the question so that the title reflects the problem. — , Mar 03 '17 at 17:04
@Jeeped I have similar doubts about whether or not I should close this as [a duplicate](http://stackoverflow.com/q/33540794/11683). — GSerg, Mar 03 '17 at 17:07
@GSerg - Yes, I think on the balance of all things considered, this may be worth keeping for others to find. — , Mar 03 '17 at 17:11

score 2 · Accepted Answer · answered Mar 03 '17 at 17:01

Be especially careful about the regional version of your Excel. The PT-PT version uses SE.ERRO with a semicolon as a list separator while the PT-BR version uses SEERRO (and typically a comma as a list separator).

score -1 · Answer 2 · answered Mar 03 '17 at 15:56

-1

Try this....Hope it will work and make sense where you done mistake.

Sub InsertIFERROR()
    Dim R As Range
    Set R = Application.InputBox("Select a range", "Get Range", Type:=8)
    'R.Select
    For Each rCell In R.SpecialCells(xlCellTypeFormulas)

        rCell.FormulaLocal = "=SEERRO(" & Mid(rCell.FormulaLocal, 2) & ",""-"")"

    Next rCell
End Sub

answered Mar 03 '17 at 15:56

Harun24hr

30,391
4
21
36

You can also use `R.Select` but no need to use. So, I make it as comment. – Harun24hr Mar 03 '17 at 15:57
While it's good to [remove `Select`](http://stackoverflow.com/q/10714251/11683), I fail to see how it could be the solution to the OP's problem. – GSerg Mar 03 '17 at 16:01
@GSerg Why not? I tested the codes. By removing `R.Select` I use `R.SpecialCells...` rather than `Selection.SpecialCells.....` Again my codes are tested. – Harun24hr Mar 03 '17 at 16:09

Error 1004 FormulaLocal with SEERRO

2 Answers2

Linked