Excel changes a formular I define with the Range.FormulaLocal into a Formula with structured references. I do not want that, how can I change it?

52 views Asked by At

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.

0

There are 0 answers