VBA (EXCEL) - Formeln in VB mit relativem Zellbezug

[Purple-Haze]

Hallo,

ich möchte eine S-Verweis Formel in VB in mehrere Zellen (in einer Spalte) untereinander einfügen. Wie das geht, ist soweit klar:

Cells(nRow, nCol).Formula = "=VLOOKUP(A1,A2:B100,2,0)"

Das Problem ist, dass der zu suchende Wert natürlich nicht immer in der Zelle A1 steht, sondern in der 2. Zeile der Wert in A2 steht, in der 3. Zeile in A3 ..... usw.

Auch die Matrix, in der der Wert gesucht werden soll, ist flexibel. Es handelt sich nämlich um ein dynamisches Tabellenblatt, dass sich ständig erweitert und ich möchte nicht ständig manuell diese Formeln aktualisieren.

Ich lasse also per VBA die belegten Zeilen zählen, und möchte diese Variable auch nutzen, um die Formeln dynamisch zu gestalten.

Also, weiss jemand, wie ich diesen Teil der Formel :

(A1,A2:B100,2,0)

dynamisch gestalten kann, oder ist es nicht möglich, diese Formel variabel in VB zu programmieren?

Vielen Dank für Eure Hilfe

Purple-Haze

 

[Orakel]

Hi Purple-Haze,

in etwa so:

    Dim nRow                As Integer
    Dim szSearchCell        As String
    Dim szSearchRange       As String
    Dim szFormula           As String

    For nRow = 1 To 10
        szSearchCell = Cells(nRow, 2).Address
        szSearchRange = Cells(nRow + 1, 1).Address & ":B100"
        szFormula = "=VLOOKUP(" & szSearchCell & "," & szSearchRange & ",2,0)"
        Cells(nRow, 5).Formula = szFormula
    Next nRow

Ich habs mal ein bischen auseinander gezogen. Optimieren kannst Du das ja glaube ich selber.

Gruß
Das Orakel

 

[Purple-Haze]

Vielen Dank Orakel,

ich werde es ausprobieren und gebe Dir dann ein Feedback.

Purple-Haze

 

[Purple-Haze]

Hi Orakel,

es sieht so aus, als würde das gut funktionieren, auch wenn ich durch das System noch nicht ganz durchblicke.

Das Problem ist nun, dass die Formel mit dem S-Verweis in einem anderen Tabellenblatt steht, als die Matrix.

Wo muss ich das in dem Programm berücksichtigen ?

Purple-Haze

 

[Orakel]

Hi,

um die Zellen in einem anderen Sheet zu beschreiben, schreibst Du folgendes:

Sheets("DeinTabellenName").Cells(nRow, 5).Formula = szFormula

Gruß
Das Orakel

 

[Purple-Haze]

Roger.

Vielen Dank. Hat funktioniert.

Purple-Haze

 

[Noretete]

Hallo,
ich hab dein Beispiel hier mal aufgenommen und es für meine Zwecke verändert.

szFormula = "=Summe(" & szSearchCell & ":" & szSearchRange & ")"

Mein Problem ist jetzt nur, das die Formal zwar richtig ist, aber er in dem Feld #Name anzeigt wird.
Klickt man allerdings mit der Maus darein und drückt dann enter funktioniert die Formel ohne Probleme.
Kann mir da jemand helfen und sagen woran das liegt und wie ich es ohne reinklicken und so richtig angezeigt kriege?

Danke
Gruß Ramona

 

[Noretete]

Hab die Lösung nun selber herausgefunden.