Microsoft Excel

Herbers Excel/VBA-Archiv

VBA: SVerweis mit Left() as Matrixformel

Betrifft: VBA: SVerweis mit Left() as Matrixformel von: Robert
Geschrieben am: 02.09.2014 15:00:22

Hallo zusammen,

Ich ruder mal wieder auf dem Trockenen vor mich hin.

Ich möchte einen SVerweis für eine ganze Spalte via VBA Durchführen. Dabei ist es mir egal, ob am Ende die SVerweisformel oder nur der Wert in Output steht (muss nicht dynamisch sein)

SVerweis via VBA geht meines Wissens so:

ZielSpalte = WorksheetFunction.VLookup(WerteSpalte, SuchMatrix, 2, False)

Funktioniert soweit gut.

Nun möchte ich aber, dass in der Zielmatrix nur die ersten 12 Zeichen gesucht werden. Dafür sind mir im Formelumfeld 2 Möglichkeiten bekannt:

1.Möglichkeit: Suchen nach Suchwert mit nachgestelltem asterix *
=SVERWEIS(SuchWert &"*";Suchmatrix;2;0)

Frage: Funktioniert das auch, wenn ich statt eines SuchWertes eine SuchRange habe? Und Wenn ja, wie.
Frage2: Wie schreibe ich die Geschichte mit dem Asterix in die .Vlookup Formel in VBA?

2. Möglichkeit: Eine Matrixformel benutzen und der Suchmatrix Links() voranstellen
{=SVERWEIS(WerteSpalte;LINKS(Suchmatrix;12);2;0)}

Frage: Wie schreibe ich sowas in VBA? Matrixformel über .FormulaArray? aber wie genau? Ich bin was die Syntax von Formeln in VBA angeht sehr unsicher.

Viele Grüße
Robert

  

Betrifft: AW: VBA: SVerweis mit Left() as Matrixformel von: fcs
Geschrieben am: 03.09.2014 00:59:21

Hallo Robert,

die Funktionen in den Zellen einer Tabelle sind normalerweise schneller als die gleiche Funktion unter VBA. D.h. Es macht ggf. Sinn die Formeln per VBA in den Ergebniszellen einzufügen. Nach dem Einfügen der Formeln ersetzt man die Formeln durch Werte, um ggf. lange Neuberechnungszeiten zu vermeiden.
Die korrekte Formelsyntax bekommt man meist gut hin, indem man die Formel für die 1. Zelle des Ergebnisbereiches mit dem Makro-Rekorder aufzeichnet. Dabei wird die Formel im allgemeinen im US-Format und in der R1C1-Schreibweise aufgezeichnet. Bei der Formeleingabe muss du darauf achten, dass die absoluten und relativen Bezüge in den Formelteilen für das anschliessenden Kopieren/Vervielfachen der Formel korrekt gesetzt werden.

Es kann manchmal Sinn machen, bei sehr vielen Zeilen, alle Berechnungen innerhalb von VBA in Datenarrays durchzuführen.

Nachfolgend Beispiele für beide Varianten.

Gruß
Franz

Sub prcFormelSverweis()
'
' prcFormelSverweis Makro
'
    Dim wks As Worksheet, rngErgebnis As Range, Zeile As Long, StatusCalc As Long
'
'per Makrorecorder aufgezeichnete Formel für 1. Ergebnis-Zelle B2, Suchwert in A2
'    Range("B2").Select
'    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1] &""*"",Daten!R2C1:R22C2,2,FALSE)"
    Set wks = Worksheets("Ergebnisse")  ' = ActiveSheet
    'Makro-Bremsen lösen
    With Application
        .ScreenUpdating = False
        StatusCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    With wks
        'letzte Zeile mit Suchwerten in Spalte A
        Zeile = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rngErgebnis = .Range(.Cells(2, 2), .Cells(Zeile, 2))
        With rngErgebnis
            .FormulaR1C1 = "=VLOOKUP(RC[-1] &""*"",Daten!R2C1:R22C2,2,FALSE)"
            .Calculate
            'Formeln durch Werte ersetzen
            .Value = .Value
        End With
    End With
    'Makro-Bremsen zurücksetzen
    With Application
        .ScreenUpdating = True
        .Calculation = StatusCalc
    End With
End Sub

Sub prcVBASverweis()
' Ergebnisse inerhalb von Daten-Array auswerten
    Dim wksErgebnis As Worksheet, wksData As Worksheet
    Dim rngSuchwerte As Range, rngErgebnisse As Range, rngMatrix As Range
    Dim arrSuchwerte, arrErgebnisse, arrMatrix
    Dim Zeile_S As Long, Zeile_M   As Long, Zeile As Long, StatusCalc As Long
    
    Set wksErgebnis = Worksheets("Ergebnisse")
    Set wksData = Worksheets("Daten")
    
    'Makro-Bremsen lösen
    With Application
        .ScreenUpdating = False
        StatusCalc = .Calculation
        .Calculation = xlCalculationManual
    End With
    'Matrix-Wertebereich setzen und Werte in Array einlesen
    With wksData
        Zeile = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rngMatrix = .Range(.Cells(2, 1), .Cells(Zeile, 2))
        arrMatrix = rngMatrix
        'Suchspalte in Matrix auf  Länge 12 einkürzen
        For Zeile_M = LBound(arrMatrix, 1) To UBound(arrMatrix, 1)
            arrMatrix(Zeile_M, 1) = Left(arrMatrix(Zeile_M, 1), 12)
        Next Zeile_M
    End With
    'Such- und Ergebniswertebereich setzen und in Arrays einlesen
    With wksErgebnis
        'letzte Zeile in Spalte mit Suchwerten - Spalte A (1)
        Zeile = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rngSuchwerte = .Range(.Cells(2, 1), .Cells(Zeile, 1))
        Set rngErgebnisse = .Range(.Cells(2, 2), .Cells(Zeile, 2))
        rngErgebnisse.ClearContents
        
        arrSuchwerte = rngSuchwerte
        arrErgebnisse = rngErgebnisse
    End With
    'Suchwerte abarbeiten
    For Zeile_S = LBound(arrSuchwerte) To UBound(arrSuchwerte)
        For Zeile_M = LBound(arrMatrix, 1) To UBound(arrMatrix, 1)
            If arrSuchwerte(Zeile_S, 1) = arrMatrix(Zeile_M, 1) Then
                arrErgebnisse(Zeile_S, 1) = arrMatrix(Zeile_M, 2)
                Exit For
            End If
        Next Zeile_M
    Next Zeile_S
    'Ergebnis-Array in Tabelle eintragen
    rngErgebnisse = arrErgebnisse
    'Makro-Bremsen zurücksetzen
    With Application
        .ScreenUpdating = True
        .Calculation = StatusCalc
    End With
    Erase arrSuchwerte, arrErgebnisse, arrMatrix
End Sub




  

Betrifft: UBound sagt "Typen unverträglich" von: Robert
Geschrieben am: 03.09.2014 13:59:03

Hallo Frank,

Vielen Lieben Dank für deine Antwort.
Ich hab nun also beschlossen mich doch noch mal an die Arrays heranzutasten.

Hier klemmts:

Set rngOutputPartNo = .Range(.Cells(3, 4), .Cells(.UsedRange.SpecialCells(xlCellTypeLastCell). _
Row, 4))
Set arrOutputPartNo = rngOutputPartNo
For x = LBound(arrOutputPartNo) To UBound(arrOutputPartNo)
    arrOutputPartNo(x) = Left(arrOutputPartNo(x), 12)
Next x
rngOutputPartNo = arrOutputPartNo
Und Zwar will er mir den LBound und UBound nicht auf mein Array Durchführen -> "Typen unverträglich"
Ich hab doch den gleichen Code verwendet wie du?

Viele Grüße
Robert


  

Betrifft: nvm von: Robert
Geschrieben am: 03.09.2014 14:22:55

arr = rng und nicht set arr = range

funktioniert jetzt..


 

Beiträge aus den Excel-Beispielen zum Thema "VBA: SVerweis mit Left() as Matrixformel"