Formel über VBA einfügen - Zeilenzahl erkennen
Jens
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:
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