Mit Makro auf zweites Tabellenblatt verweisen

Bild

Betrifft: Mit Makro auf zweites Tabellenblatt verweisen
von: Christoph
Geschrieben am: 13.09.2015 11:42:45

Hallo liebes Forum,
ich habe mal wieder ein kleines Problem.
Folgendes Makro wurde ausgetueftelt um Werte aus einem Blatt als durchschnittswert auf ein anderes Blatt zu uebertragen. Leider funktioniert die Geschichte noch nicht ganz. Bisher werden mir die Durchschnittswerte immer im gleichen Tabellen-Blatt ausgegeben wie in dem in welchem die Eingabe erfolgt. Ich komm einfach nicht auf meinen Fehler.


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCell As Range
    
    Set KeyCells = Range("N9:N50")
      
                If Intersect(KeyCells, Range("N9:N50")) Is Nothing Then Exit Sub
                ActiveSheet.Unprotect "heute"
                Application.EnableEvents = False
                
                With Sheets("Pipeline")
                 
   Range("Z10:Z100").FormulaR1C1 = _
        "=IFERROR(AVERAGEIF(Quotation!R9C3:R28C16,Pipeline!RC[-23],Quotation!R9C14:R32C14),"""") _
 _
 _
 _
"
    Range("Z10:Z40").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                End With
                Application.EnableEvents = True
                ActiveSheet.Protect "heute"
       
End Sub

Vielen Dank im Voraus fuer einen Korrekturvorschlag.
Viele Gruesse,
Christoph

Bild

Betrifft: AW: Mit Makro auf zweites Tabellenblatt verweisen
von: Sepp
Geschrieben am: 13.09.2015 12:07:14
Hallo Christoph,
dein Code ist etwas wirr und du hast die . vor den Range-Angaben im With-Rahmen vergessen

' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrExit

If Not Intersect(Target, Range("N9:N50")) Is Nothing Then
  Me.Unprotect "heute"
  Application.EnableEvents = False
  With Sheets("Pipeline")
    .Range("Z10:Z100").FormulaR1C1 = _
      "=IFERROR(AVERAGEIF(Quotation!R9C3:R28C16,Pipeline!RC[-23],Quotation!R9C14:R32C14),"""")"
    .Range("Z10:Z100") = .Range("Z10:Z100").Value
  End With
  Me.Protect "heute"
End If

ErrExit:

Application.EnableEvents = True
End Sub


Gruß Sepp


Bild

Betrifft: AW: Mit Makro auf zweites Tabellenblatt verweisen
von: Christoph
Geschrieben am: 13.09.2015 14:35:14
Hallo Sepp,
vielen Dank schon mal fuer die schnelle Antwort! Klappt soweit auch alles gut.
Nur bleibt mir jetzt die Formel stehen. Die wuerde ich gerne immer wieder entfernt haben. Da, wie du ja sicher schon bemerkt hast, meine VBA-Kenntnisse recht bescheiden sind habe ich die Aktion einfach mit dem Makro-Rekorder aufgezeichnet (Formel kopiert, Werte wieder eingefuegt).
Kann man das irgendwie eleganter loesen?
Vielen Dank und Gruesse,
Christoph

Bild

Betrifft: AW: Mit Makro auf zweites Tabellenblatt verweisen
von: Sepp
Geschrieben am: 13.09.2015 15:04:38
Hallo Christoph,
hatte noch ein .Value vergessen.

' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrExit

If Not Intersect(Target, Range("N9:N50")) Is Nothing Then
  Me.Unprotect "heute"
  Application.EnableEvents = False
  With Sheets("Pipeline").Range("Z10:Z100")
    .FormulaR1C1 = _
      "=IFERROR(AVERAGEIF(Quotation!R9C3:R28C16,Pipeline!RC[-23],Quotation!R9C14:R32C14),"""")"
    .Value = .Value
  End With
  Me.Protect "heute"
End If

ErrExit:

Application.EnableEvents = True
End Sub


Gruß Sepp


Bild

Betrifft: AW: Mit Makro auf zweites Tabellenblatt verweisen
von: Christoph
Geschrieben am: 14.09.2015 03:51:54
Hallo Sepp,
jetzt nochmal ein kleines Problem. Ich habe noch eine zweite Formel die ich ebenso per Makro ausfuerhen lassen moechte. Diese ist auch schon in den Sub eingefuegt.
An und fuer sich funktioniert die Geschichte. Leider allerdings immer nur einmal pro Formel. Danach muss Excel beendet und neu gestartet werden.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrExit
If Not Intersect(Target, Range("N9:N50")) Is Nothing Then
  Application.EnableEvents = False
  With Sheets("Pipeline").Range("Z10:Z100")
    .FormulaR1C1 = _
      "=IFERROR(AVERAGEIF(Quotation!R9C3:R28C16,Pipeline!RC[-23],Quotation!R9C14:R32C14),"""")"
    .Value = .Value
  End With
  
  
Else
If Not Intersect(Target, Range("M9:M100")) Is Nothing Then
  Application.EnableEvents = False
With Sheets("Pipeline").Range("Y10:Y100")
.FormulaR1C1 = _
        "=IFERROR(SUMIF(Quotation!R9C3:R28C16,Pipeline!RC[-22],Quotation!R9C13:R28C13),"""")"
        .Value = .Value
End With
End If
ErrExit:
Application.EnableEvents = True
End If
End Sub
Da wuerde noch einiges dazu kommen. Fuer eine kurze Anmerkunug was falsch gemacht wurde bin ich sehr dankbar. Dann muss ich auch nicht immer ankommen.
Vielen Dank und Gruesse,
Christoph

Bild

Betrifft: AW: Mit Makro auf zweites Tabellenblatt verweisen
von: Sepp
Geschrieben am: 14.09.2015 23:12:35
Hallo Christoph,
stelle jeden IF-Block einzeln auf.

' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrExit
Application.EnableEvents = False

If Not Intersect(Target, Range("N9:N50")) Is Nothing Then
  With Sheets("Pipeline").Range("Z10:Z100")
    .FormulaR1C1 = _
      "=IFERROR(AVERAGEIF(Quotation!R9C3:R28C16,Pipeline!RC[-23],Quotation!R9C14:R32C14),"""")"
    .Value = .Value
  End With
End If

If Not Intersect(Target, Range("M9:M100")) Is Nothing Then
  With Sheets("Pipeline").Range("Y10:Y100")
    .FormulaR1C1 = _
      "=IFERROR(SUMIF(Quotation!R9C3:R28C16,Pipeline!RC[-22],Quotation!R9C13:R28C13),"""")"
    .Value = .Value
  End With
End If

ErrExit:
Application.EnableEvents = True
End Sub


Gruß Sepp


Bild

Betrifft: AW: Mit Makro auf zweites Tabellenblatt verweisen
von: Christoph
Geschrieben am: 15.09.2015 10:20:51
Hallo Sepp,
Danke nochmals!

Bild

Betrifft: AW: Mit Makro auf zweites Tabellenblatt verweisen
von: Christoph
Geschrieben am: 13.09.2015 15:30:46
Hallo Sepp,
passt perfekt! Danke!
Gruss,
Christoph

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Mit Makro auf zweites Tabellenblatt verweisen"