Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
416to420
416to420
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Formel über VBA einfügen - Zeilenzahl erkennen

Formel über VBA einfügen - Zeilenzahl erkennen
Jens
Hallo Kollegen,
ich möchte in eine Excel-Tabelle über ein VBA-Programm eine neue Zeile einfügen, in welcher in Spalte F eine Formel eingefügt werden soll.
Diese Formel ist eine Verweisformel in der als "Zeilenindex" die Zeilenzahl übergeben werden soll (immer aktuell, auch beim weiteren einfügen einer neuen Zeile).
Schaut Euch zur Erklärung die angehängte Datei an:
Userbild
Des Weiteren muss sich die Formel im Suchbereich "Matrix" variabel erweitern.
Zum Punkt Zeilenzahl habe ich folgendes probiert!
In Modul:
Function MyVer()
MyVer = Application.ThisCell.Row
End Function
Funktioniert aber leider erst ab Excel 2002. D.h. ich sollte da eine andere lösung haben, damit ich es unter jeder Excel-Version zum laufen bekomme.
Zur Formel habe ich das ausprobiert:
' Formel einfügen
Range("G3").FormulaR1C1 = "=HLOOKUP(R1C7,R1C9:R61C26,myver())"
'=WVERWEIS($G$1;$I$1:$Z$61;F2)
Hierbei sollte nun $I$1:§Z§61 variabel sein und sich erweitern.
Z.B. $I$1:§Z§62
$I$1:§Z§65
......
usw.
Hier noch meine komplette VBA Programmierung wenn das jemandem hilft:

Private Sub OK_Click()
_________________________________________________________________________
' Daten aus Eingabefeld einfügen
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A3").Value = tb_gem.Text
Range("B3").Value = tb_flur.Text
Range("C3").Value = tb_schlagnr.Text
Range("D3").Value = tb_kat.Text
Range("E3").Value = tb_ha.Text
Range("G3").Value = tb_schlag.Text
'_________________________________________________________________________
' Formel einfügen
Range("G3").FormulaR1C1 = "=HLOOKUP(R1C7,R1C9:R61C26,myver())"
'=WVERWEIS($G$1;$I$1:$Z$61;F2)
'ActiveCell.FormulaR1C1 = "=HLOOKUP(R1C7,R1C9:R61C26,myver())"
'Range("G3").Select
'Selection.AutoFill Destination:=Range("G2:G3"), Type:=xlFillDefault
'Range("G49:G50").Select
'_________________________________________________________________________
' Fenster schließen
Me.Hide
'_________________________________________________________________________
' Spalten nach B sortieren
ActiveWindow.SmallScroll Down:=-84
Rows("2:200").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub


Private Sub Abbruch_Click()
Me.Hide
End Sub

AW: Formel über VBA einfügen - Zeilenzahl erkennen
ChrisL
Hi Jens
Meinst du...
Application.Caller.Row
?
Gruss
Chris
AW: Formel über VBA einfügen - Zeilenzahl erkennen
Jens
Hi Chris,
prima, danke, so funtioniert die Formel.
Jetzt noch zu meiner anderen Frage, wie ich den Bereich Variabel erweitern kann.
Danke Gruß Jens
AW: Formel über VBA einfügen - Zeilenzahl erkennen
ChrisL
Hi Jens
Du musst dir die Formel als String zusammensetzen... vom Ansatz her so...
Cells(myver(), 7).Formula = "=HLOOKUP(" & Cells(myver(), 7) & "," & ...
Gruss
Chris
AW: Formel über VBA einfügen - Zeilenzahl erkennen
Jens
Hi Chris,
die Formel funktioniert soweit ich habe sie nun wie folgt programmiert:
Range("F3").FormulaR1C1 = "=HLOOKUP(R1C6,R1C8:R61C25,myver())"
Jetzt sollte der Bereich ",R1C8:R61C25," sich automatisch variabel erweitern.
D.h. die R61 sollte sich bei jeder neue eingefügten Zeile erweitern. Z.B. 1 Zeile eingefügt R62, 2 Zeile eingefügt R63, 5 Zeile eingefügt R66, usw.
Könntest du mir das ziemlich genau beschreiben?
Danke und Gruß
Anzeige
AW: Formel über VBA einfügen - Zeilenzahl erkennen
ChrisL
Hi Jens
Versuch mal so...
Range("F3").FormulaR1C1 = "=HLOOKUP(R1C6,R1C8:R" & Range("F65536").End(xlUp).Row -2 & "61C25,myver())"
Gruss
Chris
AW: Formel über VBA einfügen - Zeilenzahl erkennen
21.04.2004 23:27:23
Jens
Hi Chris,
danke für deine Hilfe. Funktioniert hervorragend.
Gruß Jens

353 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige