Office: sverweis in macro einbetten ?

ersteinmal > guten tag alle zusammen.

ich hatte mir vor einiger zeit zu einer tabelle mehrere macros geschrieben. eins davon siehe unten.

nun wollte ich dieses macro mit einer sverweis formel erweitern.

Code:

=WENN(ISTNV(SVERWEIS(B2;Daten!B:C;2;0));"";SVERWEIS(B2;Daten!B:C;2;0))

und zwar soll spalte 2 beim ausführen des macros abgefragt werden und das ergebnis in spalte 10 eingfügt werden
(ActiveCell.FormulaR1C1 = " ergebnis "

ich bin da schon eine weile raus, deshalb meine anfrage ob mir da jemand bei helfen könnte.

Code:

Sub Test()
' Makro am 12.04.2007 von Sabienchen geschrieben
' Tastenkombination: Strg+u
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.End(xlToLeft).Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveCell.FormulaR1C1 = "frei lassen"
    ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveCell.FormulaR1C1 = " ergebnis "
    ActiveCell.Offset(0, -9).Range("A1:Q1").Select
    With Selection.Interior
        .ColorIndex = 27
        .Pattern = xlSolid
    End With
    ActiveCell.Offset(0, 11).Range("A1").Select
End Sub

mfg Sabienchen

 

Hallo!

Meinst Du ein Makro wie das folgende?

Code:

Sub TestNeu()
   Dim RgBereich As Range
   Set RgBereich = ActiveCell.EntireRow.Cells(1)
   With RgBereich
      .Value = Date
      .Offset(0, 5).Value = "frei lassen"
      With .Offset(0, 9)
        .FormulaLocal = "=WENN(ISTNV(SVERWEIS(B2;Daten!B:C;2;0));"""";SVERWEIS(B2;Daten!B:C;2;0))"
        '.Formula = "=IF(ISNA(VLOOKUP(B2,Daten!B:C,2,0)),"""",VLOOKUP(B2,Daten!B:C,2,0))"
      End With
      With .Range("A1:Q1").Interior
        .ColorIndex = 27
        .Pattern = xlSolid
      End With
      .Offset(0, 11).Select
   End With
End Sub

Der Knackpunkt beim Zuweisen einer Formel zu einer Zelle aus VBA heraus ist unter anderem, dass man darauf achten muss, dass man jedes in der Formel enthaltene Anführungszeichen durch ein weiteres Anführungszeichen ergänzen muss.
Ferner muss man natürlich die passende FORMULA-Eigenschaft wählen, wovon es 5 Varianten gibt:
.Formula
.FormulaR1C1
.FormulaLocal
.FormulaR1C1Local
.FormulaArray
Ferner habe ich die SELECTION-Objekte durch RANGE-Objekte ersetzt und zudem "WITH...END WITH"-Anweisungsblöcke eingesetzt, um den Code zu verkürzen.
Die englischsprachige Originalformelvariante habe ich nur informationshalber (als Kommentar) eingefügt. Diese Zeile kann natürlich entfernt werden.

 

soweit hab ich es einigermassen verstanden.

ich habe eine liste erstellt auf dem blatt " Daten "
spalte B und C

___B___ ___C___
442 100 . Auto AS
442 123 . Auto BC
...

aber die formel macht nur immer den ersten wert ...also " Auto AS "
als wenn die liste nicht akzeptiert wird.

da muss noch irgendwo ein fehler drin sein.

ich habs.....
=WENN(ISTNV(SVERWEIS(B2;Daten!B:C;2;0));"""";SVERWEIS(B2;Daten!B:C;2;0))

> B2 , da sollte dann eigentlich die aktuelle zeilennummer drinstehen....

kann man die vorher abfragen ?

mfg

 

Hallo,

folgendes Makro führt die Zeilenanpassung in der Formel durch:

Code:

Sub TestNeu()
   Dim RgBereich As Range
   Dim Formel As String
   
   Set RgBereich = ActiveCell.EntireRow.Cells(1)
   With RgBereich
      .Value = Date
      .Offset(0, 5).Value = "frei lassen"
      With .Offset(0, 9)
        Formel = "=WENN(ISTNV(SVERWEIS($$;Daten!B:C;2;0));"""";SVERWEIS($$;Daten!B:C;2;0))"
        .FormulaLocal = Replace(Formel, "$$", "B" & .Row)
      End With
      With .Range("A1:Q1").Interior
        .ColorIndex = 27
        .Pattern = xlSolid
      End With
      .Offset(0, 11).Select
   End With
End Sub

 

also ist
.FormulaLocal = Replace(Formel, "$$", "B" & .Row)
dafür um die zeilennummer anzupassen.
super, muss ich doch mal damit rumprobieren :-)

aja, das makro funktioniert astrein, so wie ich es mir vorgestellt hatte.

ganz vielen lieben dank für die schnelle hilfe.


bis zu dem ergebnis hätte ich es bestimmt nicht alleine geschafft.
ich werd mich wohl jetzt doch öfters damit beschäftigen müssen.
man rostet so schnell ein...

mfg sabiene

 

guten morgen,

ich habe dann mal wieder etwas experimentiert und mir ist aufgefallen:
wenn das blatt " Daten " aus der mappe entfernt wird gibt es eine fehlermeldung "#Bezug" im feld der formel.

nun habe ich mal die suche bemüht konnte aber nichts finden, wie man nach
erfolgreichem eintragen des "ergebnisses", die formel
" =WENN(ISTNV(SVERWEIS($$;Daten!B:C;2;0));"""";SVERWEIS($$;Daten!B:C;2;0))"
wieder entfernt ohne das ergebnis zu löschen.

weis da jemand wie ich das lösen könnte ?

mfg Sabiene

 

auslesen (kopieren) der zelle und eintragen des Wertes (Menü: Bearbeiten - Inhalte Einfügen - nur Werte)

Code:

.cells(y,x).copy
.cells(y,x).pastespecial paste:=xlpastevalues

oder, bei einzelnen Zellen, noch einfacher:

Code:

.cells(y,x).value = .cells(y,x).value

Damit wird die Formel (Eigenschaft .formula) durch den Wert (Eigenschaft .value) ersetzt / überschrieben.

Gruß, Michael

 

guten tag,


das ist ja prima, so einfach..
so kannte ich das noch garnicht.
werde ich dann mal mit ins makro einfügen.

vielen lieben dank, sabiene

 

hallo,
ich hab es mal mit eingetragen, dürfte aber falsch sein da die formel trotzdem noch in der zelle steht.
och menno...ist doch nicht so einfach.....

Code:

Dim RgBereich As Range
Dim Formel As String
   
   Set RgBereich = ActiveCell.EntireRow.Cells(1)
   With RgBereich
      .Value = Date
      With .Offset(0, 5)
        Formel = "=WENN(ISTNV(SVERWEIS($$;Daten!B:D;3;0));""Vrg 0063"";SVERWEIS($$;Daten!B:D;3;0))"
        .FormulaLocal = Replace(Formel, "$$", "B" & .Row)
      End With
        .Cells(0, 5).Copy
        .Cells(0, 5).PasteSpecial Paste:=xlPasteValues
      With .Offset(0, 9)
        Formel = "=WENN(ISTNV(SVERWEIS($$;Daten!B:C;2;0));"" HH"";SVERWEIS($$;Daten!B:C;2;0))"
        .FormulaLocal = Replace(Formel, "$$", "B" & .Row)
      End With
        .Cells(0, 9).Copy
        .Cells(0, 9).PasteSpecial Paste:=xlPasteValues
      With .Range("A1:Q1").Interior
        .ColorIndex = 27
        .Pattern = xlSolid
      End With
      .Offset(0, 11).Select
   End With

 

sehr ungetestet:

also das .copy mit dem Bereich machen, der bereits durch das "with" bekannt ist.

Gruß, Michael

 

guten tag,

recht herzlichen dank für die hilfe.
jetzt funktionietrtb es endlich zu meiner zufriedenheit.


danke danke

mfg sabiene