0

I want to insert a formula into a sequence of cells by using a "for-loop". My formula references other cells that change for each loop. The formula itself works but when I enter it with Range.FormulaLocal it changes and my references to other workSheets become structured references. I do not want that because my formula wont work with them. How can I change this behaviour or do I have to change my formula?

    Dim aus As Range
    Dim Nr As Long
    Dim Bux As String
    Set aus = Range("C3")
    For Spalte = 3 To 15 Step 2
    For Zeile = 3 To 10 Step 1
    Nr = aus.Row
    Bux = Buchstabe(aus.Column)
    aus.Select
    Formel = "= VLOOKUP(Auswertung!A" & Nr & "&B" & Nr & "&" & Bux & "1;CHOOSE({1,2,3};Daten!A:A&Daten!B:B&Daten!C:C;Daten!D:D);2;FALSE)"
    Selection.Formula2 = Formel
    Set aus = aus.Offset(1, 0)
    Next
    Set aus = Range(Bux & "3")
    Set aus = aus.Offset(, 2)
    Next

The Formula shoudl look like this:

=SVERWEIS(Auswertung!A3&B3&C1;WAHL({1.2.3};Daten!A:A&Daten!B:B&Daten!C:C;Daten!D:D);2;FALSCH)

but ends up looking like this:

=SVERWEIS(Auswertung!A3&B3&C1;WAHL({1.2.3};@Daten!A:A&@Daten!B:B&@Daten!C:C;Daten!D:D);2;FALSCH)

English version: Expectation

=VLOOKUP(Auswertung!A3&B3&C1;CHOOSE({1.2.3};Daten!A:A&Daten!B:B&Daten!C:C;Daten!D:D);2;FALSE)

Reality:

=@ VLOOKUP(Auswertung!A4&B4&C1;CHOOSE({1.2.3};@Daten!A:A&@Daten!B:B&@Daten!C:C;Daten!D:D);2;FALSE)

Thanks in advance.

  • `.Value` is to set a value not a formula. Therefore you need to use `.Formula` and if you use non english formulas you need to use `.FormulaLocal`. But I recommend to use only english formulas in VBA because of compatibility reasons. – Pᴇʜ Feb 15 '21 at 10:46
  • 2
    FYI, that is not a structured reference (which relates to Tables). The `@` is the implicit intersection indicator. If you wrote the formula in English, you could use the `.Formula2` property to avoid the `@`. – Rory Feb 15 '21 at 11:02
  • I changed the language of the fomula and used Selection.Formula2 now it also adds the @infront of the formula –  Feb 15 '21 at 15:33
  • No, this is not an English formula. You not only have to use the English names (`VLOOKUP`), but also English list separators (`,`) and decimal dots (`.`). – GSerg Feb 15 '21 at 15:40
  • english formula won't work as i do not have the english language package. Is there a FormulaLocal2 ? –  Feb 16 '21 at 07:54
  • English formulas always work. That is why they should always be used from code, because only that way you can ensure they will work on any Excel installation. If an English formula does not work for you, then it's not a valid English formula, with all the English separators. – GSerg Feb 16 '21 at 13:47
  • Note that `CHOOSE({1.2.3}` is not correct either. Use `CHOOSE({1,2,3}` – JvdV Feb 17 '21 at 14:02

0 Answers0