Microsoft Excel

Herbers Excel/VBA-Archiv

SVERWEIS und ARRAY in ein MAKRO integrieren | Herbers Excel-Forum


Betrifft: SVERWEIS und ARRAY in ein MAKRO integrieren von: Henning
Geschrieben am: 24.11.2009 14:24:13

Hallo zusammen,
ich abe folgendes Problem.
Ein Arbeitsmappe mit drei Blättern.
Ein Blatt "Kosten Summary" hier werden die Daten zusammengefahren und zwei sog. Quellen "IMFA_ALL_MP_1" und "IMFA_INA_EP_001".
Auf diese beiden Blätter greife ich momentan händisch mit einer Formel zu.
Dieses möchte ich nun aber automatisieren, weiß aber nicht wie.

Hier meine Vorstellung:
Spalten A&B werden automatisch gefüllt und können somit unterschiedlich lange Zeilen haben. Aus der Spalte "Budget" mache ich einen SVERWEIS auf das Blatt "IMFA_ALL_MP_1" Der Einstiegspunkt F 18 bleibt gleich auch die Spalten ändern sich nicht aber die Zeilen können unterschiedlich lang sein. Kann man den SVERWEIS so in ein Makro binden, dass er immer bis zur letzten Zeile schaut?
Per Hand kein Problem:-)

Die nächste Spalte "Auftragsvolumen" benutzt ein ARRAY, um die Daten zu selektieren.
Der Einstiegspunkt ist F18 (in der Datei steht C19, ist aber definitiv F19) auch hier ist der Spaltenaufbau klar definiert nur die Zeilen sind variabel.

Kann mir mir jemand zeigen wie ich den SVERWEIS und ein ARRAY in ein Makro bauen kann?

Vielen Dank im Voraus.
Beste Grüße
maggifrosch

https://www.herber.de/bbs/user/66131.xls

  

Betrifft: AW: SVERWEIS und ARRAY in ein MAKRO integrieren von: fcs
Geschrieben am: 24.11.2009 23:15:56

Hallo Henning,

ein Makro zum Erzeugen der Formeln im Summary-Blatt sieht wie folgt aus.

Gruß
Franz

Sub FormelnSummary()
'
' FormelnSummary Makro
  Dim ZeileIMFA_ALL As Long, ZeileIMFA_INA As Long, ZeileSummary As Long
  'Letzte Zeile mit PSP-Element im Blatt "IMFA_ALL_MP_1", SPalte F (6)
  With Worksheets("IMFA_ALL_MP_1")
    ZeileIMFA_ALL = .Cells(.Rows.Count, 6).End(xlUp).Row
  End With
  'Letzte Zeile mit PSP-Element  im Blatt "IMFA_INA_EP_001", SPalte F (6)
  With Worksheets("IMFA_INA_EP_001")
    ZeileIMFA_INA = .Cells(.Rows.Count, 6).End(xlUp).Row
  End With
  With Worksheets("Kosten Summary")
    'Letzte Zeile mit PSP-Element  im Blatt "Kosten Summary", SPalte B (2)
    ZeileSummary = .Cells(.Rows.Count, 2).End(xlUp).Row
    'Formel in Zelle "D7"
    .Cells(7, 4).FormulaR1C1 = _
      "=IF(ISNA(VLOOKUP(RC2,IMFA_ALL_MP_1!R18C6:R" & ZeileIMFA_ALL _
        & "C77,16,FALSE)),"""",(VLOOKUP(RC2,IMFA_ALL_MP_1!R18C6:R" _
        & ZeileIMFA_ALL & "C77,16,FALSE)))"
    .Range(.Cells(7, 4), .Cells(ZeileSummary, 4)).FillDown
    'Formel in Zelle "G7"
    .Cells(7, 7).FormulaArray = _
      "=SUM(IF(RC2=IMFA_INA_EP_001!R19C6:R" & ZeileIMFA_INA _
      & "C6,IMFA_INA_EP_001!R19C24:R" & ZeileIMFA_INA & "C24,0))"
    .Range(.Cells(7, 7), .Cells(ZeileSummary, 7)).FillDown
    'Formel in Zelle "H7"
    .Cells(7, 8).FormulaArray = _
      "=SUM(IF(RC2=IMFA_INA_EP_001!R19C6:R" & ZeileIMFA_INA _
      & "C6,IMFA_INA_EP_001!R19C26:R" & ZeileIMFA_INA & "C26,0))"
    .Range(.Cells(7, 8), .Cells(ZeileSummary, 8)).FillDown
  End With
End Sub



  

Betrifft: AW: SVERWEIS und ARRAY in ein MAKRO integrieren von: Henning
Geschrieben am: 25.11.2009 14:35:33

Hallo Franz,

vielen, vielen Dank. Funktioniert einwandfrei.

Nur noch eine Frage, wo bzw. wie kann ich die Spaltenwahl ändern. Das das Makro beim SVerweis (VLOOKUP) mal in Spalte AA bzw. X suchen soll. Es stellte sich heute nämlich heraus das die Spalten sich noch mal geändert haben und mir ist irgendwie nicht ersichtlich wo ich den Spaltennamen änder kann.

Vielen Dank

Gruß

Henning


  

Betrifft: AW: SVERWEIS und ARRAY in ein MAKRO integrieren von: fcs
Geschrieben am: 25.11.2009 18:00:08

Hallo Henning

die Spalten sind hier immer die Ziffern hinter dem C; in z.B RC2 bedeutet C2 = 2. Spalte (Column) also Spalte B.

Die Zelladressen sind hier also immer in der Z1S1 = R1C1 -Schreibweise angegeben.
Ist -wenn man nur die A1-Zelladressierung kennt- gewöhnungsbedürftig, läßt sich aber unter VBA wesentlich einfacher programmieren. Vergleichbar zu Zelladressen, die man ja als Range("A1") oder Cells(1,1) angeben kann.

Gruß
Franz

Gruß
Franz


Beiträge aus den Excel-Beispielen zum Thema "SVERWEIS und ARRAY in ein MAKRO integrieren"