Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
776to780
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
776to780
776to780
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

formel durch makro automatisch ersetzen

formel durch makro automatisch ersetzen
24.06.2006 00:20:25
Dörte
Hallo zusammen,
ich habe nun zig Stunden mit meiner Reserche im Archiv verbracht und bin doch kein Stück weiter. Vielleicht kann mir ja jemand helfen.
A1 = Artikelnummer B1 =WENN(ISTZAHL(A1);SVERWEIS(A1;Grund;2;FALSCH);"")
nun möchte ich, das B1 automatisch über ein makro reagiert und in VBA diese formel ausführt.
mein code hierfür: im Klassenmodul

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then
Call Makro1
End If
End Sub

mein code hierfür: im Modul
Sub Makro1()
Range("B1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(RC[-1]),VLOOKUP(RC[-1],Grund,2,FALSE),"""")"
End Sub
Das funktioniert soweit auch alles. Allerdings möchte ich vermeiden, das sich die Datei unnötig aufplustert und dachte es über VBA erreichen zu können. Leider wird durch diesen Code immer noch die Formel in Zelle B1 eingetragen.
Vielleicht kann mir ja jemand helfen.
PS: Dieses Forum finde ich übrigens super. Vieles erledigt sich schon durch die Reserche. Dickes Lob an Herber
Gruß Dörte

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: formel durch makro automatisch ersetzen
24.06.2006 00:57:38
Helga

Sub Makro1()
[B1].FormulaR1C1 = _
"=IF(ISNUMBER(RC[-1]),VLOOKUP(RC[-1],Grund,2,FALSE),"""")"
[B1] = [B1].Value
End Sub

mfg Helga
AW: formel durch makro automatisch ersetzen
24.06.2006 02:05:16
Dörte
Hallo Helga,
danke für die schnelle Hilfe. Leider funktioniert es noch nicht. Wenn ich die Zelle B1 aktiviere, dann habe ich ne Endlosschleife drin, der Curser ist voll am abdrehen und hört nicht wieder auf zu blinken.
Gruß Dörte
AW: formel durch makro automatisch ersetzen
24.06.2006 02:05:23
Dörte
Hallo Helga,
danke für die schnelle Hilfe. Leider funktioniert es noch nicht. Wenn ich die Zelle B1 aktiviere, dann habe ich ne Endlosschleife drin, der Curser ist voll am abdrehen und hört nicht wieder auf zu blinken.
Gruß Dörte
Anzeige
AW: formel durch makro automatisch ersetzen
24.06.2006 02:05:27
Dörte
Hallo Helga,
danke für die schnelle Hilfe. Leider funktioniert es noch nicht. Wenn ich die Zelle B1 aktiviere, dann habe ich ne Endlosschleife drin, der Curser ist voll am abdrehen und hört nicht wieder auf zu blinken.
Gruß Dörte
AW: formel durch makro automatisch ersetzen
24.06.2006 02:05:48
Dörte
Hallo Helga,
danke für die schnelle Hilfe. Leider funktioniert es noch nicht. Wenn ich die Zelle B1 aktiviere, dann habe ich ne Endlosschleife drin, der Curser ist voll am abdrehen und hört nicht wieder auf zu blinken.
Gruß Dörte
AW: formel durch makro automatisch ersetzen
24.06.2006 02:05:51
Dörte
Hallo Helga,
danke für die schnelle Hilfe. Leider funktioniert es noch nicht. Wenn ich die Zelle B1 aktiviere, dann habe ich ne Endlosschleife drin, der Curser ist voll am abdrehen und hört nicht wieder auf zu blinken.
Gruß Dörte
Anzeige
AW: formel durch makro automatisch ersetzen
24.06.2006 02:06:29
Dörte
Hallo Helga,
danke für die schnelle Hilfe. Leider funktioniert es noch nicht. Wenn ich die Zelle B1 aktiviere, dann habe ich ne Endlosschleife drin, der Curser ist voll am abdrehen und hört nicht wieder auf zu blinken.
Gruß Dörte
AW: formel durch makro automatisch ersetzen
24.06.2006 07:37:58
schauan
Hallo Dörte,
das kann eigentlich nur passieren, wenn sich in einer Zelle was ändert weil sich B1 geändert hat. Dann läuft das Ding bis irgendwann. Wenn sich hingegen in vielen anderen Zellen was ändert läuft es auch länger, hört aber auch wieder auf.
Übrigens ist für Excel eine Änderung nicht nur, dass was anderes drinsteht. Da reicht ein Abschluss der Eingabe z.B. mit ENTER oder eine Neuberechnung mit gleichem Ergebnis aus.
Etwas reduzieren kann man das so:


Sub Makro1()
Application.EnableEvents = False
[B1].FormulaR1C1 = _
"=IF(ISNUMBER(RC[-1]),VLOOKUP(RC[-1],Grund,2,FALSE),"""")"
[B1] =[B1].Value
Application.EnableEvents = True
End Sub

     Code eingefügt mit Syntaxhighlighter 4.0


Hoffe geholfen zu haben
Grüße von André aus Gera - Excel-97-2003

Anzeige
AW: formel durch makro automatisch ersetzen
25.06.2006 21:33:16
Dörte
hallo zusammen,
ich finde dieses forum echt super und fühle mich hier auch gut aufgehoben.
danke für die eure antworten. hab es gleich mal ausprobiert mit dem makro von franz und das hat super geklappt.
nun hab ich noch ein kleines problem. wie bekomme ich es hin, das dieses makro für die ganze spalte funktioniert? muß ich für jede einzelne zelle ein eigenes makro anlegen oder geht das über ne schleife? da über das ganze blatt berechnungen erfolgen sollen, wird mir die datei über den normalen formelweg zu groß.
gruß dörte
AW: formel durch makro automatisch ersetzen
25.06.2006 23:04:11
fcs
Hallo Dörte,
folgende Anpassungen sind notwendig:

im Tabellenmakro:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column <> 2 Then 'verschieden von Spalte B
'  If Target.Column = 1 Then '= Spalte A
Call Makro1
End If
Application.EnableEvents = True
End Sub
in der Subroutine:
Sub Makro1()
Dim wks As Worksheet, Zeile As Long
Set wks = ActiveWorkbook.Sheets("Tabelle1") 'Tabelle in der die Werte in Spalte A eingetragen werden.
With wks
Zeile = .Cells(.Rows.Count, "A").End(xlUp).Row 'Letzte ausgefüllte Zeile in Spalte A
.Range(.Cells(1, "B"), .Cells(Zeile, "B")).FormulaR1C1 = _
"=IF(ISNUMBER(RC[-1]),VLOOKUP(RC[-1],Grund,2,FALSE),"""")"
.Range(.Cells(1, "B"), .Cells(Zeile, "B")) = .Range(.Cells(1, "B"), .Cells(Zeile, "B")).Value
End With
End Sub

Dabei solltest du dir Überlegen, ob es ausreicht nur zu überwachen, ob in Spalte A ein Wert geändert wurde. Insbesondere wenn nicht ständig im Bereich "Grund" Änderungen eingetragen werden halte ich das für sinnvoll, da dann nicht ständig die Spalte B neuberechnet werden muß.
mfg
Franz
Anzeige
AW: formel durch makro automatisch ersetzen
27.06.2006 22:23:40
Dörte
hallo franz,
danke für die superhilfe, bin leider erst heute zum testen gekommen. soweit hat auch alles funktioniert, nur leider setzt es ein anderes makro außer kraft und ich kann mir einfach nicht erklären warum.
hier mal das makro, welches außer kraft gesetzt wurde

Sub FillFormular_b(ZielSheetName) 'Artikelnummer Intern
Dim QuellBereich, ZielBereich As Object
Set QuellBereich = Worksheets("Eingabe").Range("c2:p2000") 'Range("Datenbank")
Const Zielspalte As Integer = 1
'Const StartZielZeile As Integer = 10
Const iLNr As Integer = 10 'index der Spalte Lieferantennr
Const iANr As Integer = 1 'index der Spalte Artikelnummer
Const iZNr As Integer = 1 'index der Zielspalte
Dim ZielSheet As String
Dim LNr, QuellZeile, ZielZeile As Integer
Set ZielBereich = Worksheets(ZielSheetName).Range("b7:b2000")
LNr = Worksheets(ZielSheetName).Range("b3").Value 'hole LNr-Vorgabe
If IsNumeric(LNr) Then
'Zielbereich löschen
With ZielBereich
.Value = ""
End With
ZielZeile = 1
QuellZeile = 3
'gehe über die quelltabelle, bis die Lieferantenspalte keine nummer mehr enthält
Do While IsNumeric(QuellBereich.Cells(QuellZeile, iLNr).Value)  HIER STOCKT ES
'entspricht die Lieferantennummer der vorgabe?
If (QuellBereich.Cells(QuellZeile, iLNr).Value = LNr) Then
'fülle die nächste zielzeile mit der gefundenen artikelnummer
ZielBereich.Cells(ZielZeile, iZNr).Value = QuellBereich.Cells(QuellZeile, iANr).Value
ZielZeile = ZielZeile + 1
End If
QuellZeile = QuellZeile + 1
Loop
End If
End Sub

kann es sein, daß dieses mit der zuordnung .Cells zu tun hat?
die eingabe der daten erfolgt unsortiert (ca 40 lieferanten) im eingabeblatt. für die übertragung auf die einzelnen lieferantenblätter brauche ich dieses makro. damit habe ich mir schon ne menge formeln erspart und die datei kleiner bekommen.
gruß dörte
Anzeige
AW: formel durch makro automatisch ersetzen
28.06.2006 13:45:01
fcs
Hallo Dörte,
es sieht so aus als ob das Schreiben von Daten in den Zielbereich der Tabelle "ZielSheetName" das Makro auslöst, das auf Änderung von Werten reagiert.
Evtl. reicht es zu Beginn des Makros folgende Zeile einzufügen:
Application.EnableEvents = False
und am Ende
Application.EnableEvents = True
Dadurch werden die Makos, die auf Ereignisse reagieren, vorübergehend außer Kraft gesetzt.
mfg
Franz
AW: formel durch makro automatisch ersetzen
28.06.2006 20:50:28
Dörte
hallo franz,
leider funktioniert das nicht, oder ich füge es an einer falschen stelle ein. keine ahnung, aber danke für die hilfe. vielleicht hast du ja noch einen anderen tip.
gruß dörte
Anzeige
AW: formel durch makro automatisch ersetzen
24.06.2006 08:16:50
fcs
Hallo Dörte,
so wie du die beiden Makros einsetzt, kommt es zu einer Endlosschleife da sich der Wert von B1 ändert und das Change_Ereignis neu ausgelöst wird.
Abhilfe: Ändere das Makro in der Tabelle wie folgt:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address <> "$B$1" Then
Call Makro1
End If
Application.EnableEvents = True
End Sub

Du könntes aber auch das ganze etwas anders machen und den Wert für B1 im Makro berechnen ohne den Umweg über die Formel.

'Bereich "Grund" und Zelle "A1" sind in der gleichen Tabelle
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("$A$1"), Application.Range("Grund")) Is Nothing Then
If IsNumeric(Range("A1")) Then
Range("B1").Value = Application.WorksheetFunction.VLookup(Range("A1"), Application.Range("Grund"), 2, False)
Else
Range("B1").Value = ""
End If
End If
End Sub

Sind die Zelle A1 und der Bereich "Grund" in verschiedenen Tabellen, dann müssen in beiden Tabellen Makros angelegt werden. Dadurch wird sichergestellt, dass Änderungen im Bereich Grund auch den Wert in Zelle B1 aktualisieren.

'Makro in Tabelle 1 (Tabelle mit Eingabezelle A1)
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address= "$A$1" Then
If IsNumeric(Target) Then
Target.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(Target.Value, Application.Range("Grund"), 2, False)
Else
Target.Offset(0, 1).Value = ""
End If
End If
End Sub
' makro in Tabelle 2 mit Bereichsnamen "Grund"
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Application.Range("Grund")) Is Nothing Then
With Sheets("Tabelle1")
If IsNumeric(.Range("A1")) Then
.Range("B1").Value = Application.WorksheetFunction.VLookup(.Range("A1"), Application.Range("Grund"), 2, False)
Else
.Range("B1").Value = ""
End If
End With
End If
End Sub

mfg
Franz

Anzeige
AW: formel durch makro automatisch ersetzen
24.06.2006 08:25:06
schauan
Hallo Franz,
knapp daneben ist auch vorbei. Im Change-Ereignis steht <> B1 ! Siehe meine Antwort.
Ansonsten ist die Berechnung im Makro manchmal der bessere Weg. Manchmal rechnet aber auch Excel per Formel auf dem Blatt schneller ;-)

Hoffe geholfen zu haben
Grüße von André aus Gera - Excel-97-2003

AW: formel durch makro automatisch ersetzen
24.06.2006 08:25:13
schauan
Hallo Franz,
knapp daneben ist auch vorbei. Im Change-Ereignis steht <> B1 ! Siehe meine Antwort.
Ansonsten ist die Berechnung im Makro manchmal der bessere Weg. Manchmal rechnet aber auch Excel per Formel auf dem Blatt schneller ;-)

Hoffe geholfen zu haben
Grüße von André aus Gera - Excel-97-2003

Anzeige

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige