Limit Formulaarray

Asked

Viewed 131 times

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 Formulaarray does not accept include the entire formula line. I saw that it could not exceed 255 characters, so I divided and reduced to the maximum, but still not accepted.

Could you tell me where I’m going wrong, or if there really is no way to make it work with so many characters?

Thank you very much.

vba excel-vba
  • 1

    The path to the file is very long and repeats itself 3 times. One way I see to reduce this is to map the folder on a drive as something like SUBST w: C:\Users\Treinamento\Desktop\Trabalho em vba\BC\ . From that point you can reach the folder by doing only w:, then its variable Rmts would only become Rmts = "w:\[Relacionamentos.xlsx]Matriz_Trein_Obrigatórios". Now it is possible that there are other cleaner solutions that I don’t know about.

  • I can try, but I’m in doubt about the replacement. Even when I tried to replace it, either by swapping it or using a replace, I still can’t make it work. Incidentally, I tried to include the file a sheet so I wouldn’t have to search for it separately, but it still doesn’t work.

1 answer

0

In addition to what @Isac said about decreasing the path size Rmts to get 255 characters.

A replacement can be performed as follows:

  1. Any string is inserted in the Formulaarray instead of Rmts
  2. Replace this string with the desired path

In the example the string "temp" will be used, in which a temp worksheet name is created temporarily to not generate errors, then 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

Browser other questions tagged vba excel-vba

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