Limit FormulaArray

Question

Limit FormulaArray

Navigation

#1 by (0 votes)

1

Range("B11").Select
Dim Rmts As String
Dim Busca As String

Rmts = "C:\Users\Treinamento\Desktop\Trabalho em vba\BC\[Relacionamentos.xlsx]Matriz_Trein_Obrigatórios"
Busca = "'!R8C6:R394C34=Busca!R8C2,ROW('"

Range("B11:B450").FormulaArray = _
"=IFERROR(INDEX('" & Rmts & _
"'!R8C3:R394C34,SMALL(IF('" & Rmts & _
Busca & Rmts & "'!R8C6:R394)-7),ROW(R[-10]C[-1])),1),"""")"

I'm trying to make an index formula work in VBA, but the FormulaArray does not support including the entire formula line. I have already seen that it could not exceed more than 255 characters, so I divided it and reduced it to the maximum, but it still does not accept it.

Could you tell me where I'm wrong, or if you really can not make it work with so many characters?

Thank you very much.

vba excel-vba

asked by anonymous 20.09.2018 / 07:20

1 answer

How to do a Json_Decode of a file in json format Warning: mysqli_num_rows () expects parameter 1 to be mysqli_result, boolean given in [duplicate]

score 0 · Answer 1

In addition to @Isac said to decrease the size of the path Rmts to get 255 characters.

A substitution can be performed as follows:

Any string is inserted into FormulaArray instead of Rmts

Replace this string with the desired path

In the example the string "temp" will be used, in which a nameplate temp is created temporarily to not generate errors, then it is deleted.

Code

Dim Rmts As String
Dim Busca As String
Dim destino As Range
Dim ws As Worksheet, temp As Worksheet

Set temp = Worksheets.Add
temp.Name = "temp"
Set ws = ThisWorkbook.Worksheets("Planilha1")
Set destino = ws.Range("B11:B450")
With destino
    Rmts = "C:\Users\Treinamento\Desktop\Trabalho em vba\BC\[Relacionamentos.xlsx]Matriz_Trein_Obrigatórios"
    Busca = "'!R8C6:R394C34=Busca!R8C2,ROW('"
    .FormulaArray = _
                  "=IFERROR(INDEX('temp" & _
                  "'!R8C3:R394C34,SMALL(IF('" & "temp" & _
                  Busca & "temp" & "'!R8C6:R394)-7),ROW(R[-10]C[-1])),1),"""")"
    .Replace "temp", Rmts, xlPart
End With
temp.Delete