Schlagwort: INDIREKT

Einfach nicht aufgepasst!

Programmieren Sie VBA? Programmieren Sie Formeln in VBA? Auf einem Tabellenblatt befinden sich in den Spalten BJ und BL Daten, die vertikal angeordnet sind:

Auf einem anderen Blatt wird Bezug auf diese Daten genommen:

Während ich die erste Spalte nach unten ziehen kann, muss ich die Formeln der ersten Zeile getrennt eingeben – ich möchte weder mit MTRANS, INDIREKT noch mit BEREICH.VERSCHIEBEN arbeiten. Es handelt sich um maximal sechs Werte.

Die Formeln müssen per VBA neu geschrieben werden. Der Makrorekorder ermittelt für die Formel

=WENN(tbl_Basisdaten!BJ2="";"";tbl_Basisdaten!BJ2)

der Zelle A2 den VBA-Code:

ActiveCell.FormulaR1C1 = " =IF(tbl_Basisdaten!RC[61]="""","""",tbl_Basisdaten!RC[61])"

Dabei ist RC[61] ein relativer Bezug: R -> gleiche Zeile; C[61] -> Spalte um 61 Spalten verschoben. Wäre der Bezug absolut ($BJ$2) wäre der Code:

R2C62

also: in Zeile 2 und in Spalte 62 – egal, wo sich die Zielzelle befindet. Der Code wird in einer Schleife verwendet:

For i = 1 To 6
   ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(i, 0).FormulaR1C1 = _
      "=IF(tbl_Basisdaten!RC[61]="""","""",tbl_Basisdaten!RC[61])" ' -- erste Spalte A
Next i

Klappt famos! Und nun die erste Zeile. Der Makrorekorder zeichnet auf:

ActiveCell.FormulaR1C1 = _
    "=IF(tbl_Basisdaten!R[1]C[62]="""","""",tbl_Basisdaten!R[1]C[62])"

Das kann doch leicht angepasst werden:

For i = 1 To 6
    ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(0, i).FormulaR1C1 = _
        "=IF(tbl_Basisdaten!R[" & i & "]C[62]="""","""",tbl_Basisdaten!R[" & i & "]C[62])" ' -- erste Zeile 1
        
Next i

Padautz – das funktioniert nicht! In B1 greife ich auf Spalte 63 zu, also 62 Spalten „neben mir“. In C1 dagegen benötige ich die Spalte, die sich 61 Spalten neben mir befindet, in D1 dagegen 60 Spalten. Also noch einmal:

For i = 1 To 6
    ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(0, i).FormulaR1C1 = _
        "=IF(tbl_Basisdaten!R[" & i & "]C[" & (63 - i) & "]="""","""",tbl_Basisdaten!R[" & i & "]C[" & (63 - i) & "])" ' -- erste Zeile 1
Next i

Und das klappt! Man muss so aufpassen bei den Bezügen! Und beim Umdenken von =BL2 auf =R[1]C[62].