Guten MOrgen,
eine Frage habe ich noch. Da ich ja für jeden Monat eine Tabelle für die Abrechnung der Essenskosten aller Kinder erstelle wäre es blöd, dass in einer alten Liste von Januar auf einmal neue Kinder aus September vorhanden sind. Aktuell sind die tabellen ja mit der aktiven Mitglieder Liste verbunden. Gibt es einen Befehl um per Makro alle Daten aus Verknüpfungen in feste Werte umzuwandeln das statt
Code:
=WENNFEHLER(INDEX('aktive Mitglieder'!$B$4:$B$63;KKLEINSTE(WENN('aktive Mitglieder'!$H$4:$H$63="A";ZEILE($1:$60));ZEILE(A1)));"")
Der Wert z.B Arnold ... dort aufgelistet wird?
Gruß Daniel
Hi Daniel,
(16.05.2015, 07:33)Daniel Albert schrieb: [ -> ]Gibt es einen Befehl um per Makro alle Daten aus Verknüpfungen in feste Werte umzuwandeln das statt
Code:
=WENNFEHLER(INDEX('aktive Mitglieder'!$B$4:$B$63;KKLEINSTE(WENN('aktive Mitglieder'!$H$4:$H$63="A";ZEILE($1:$60));ZEILE(A1)));"")
Der Wert z.B Arnold ... dort aufgelistet wird?
Du könntest es mit einem der beiden Makros machen (Makro 1 ist nur aufgezeichnet), dann sind die Formeln in diesen Zellen aber weg:
Option Explicit
Sub Umwandeln_in_feste_Werte_1()
'
' Umwandeln_in_feste_Werte Makro
' Dieses Makro macht aus Formeln feste Werte.
'
'
Range("A13").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' kürzer
' Range("A13").Copy
' Range("A13").PasteSpecial Paste:=xlPasteValues
' Application.CutCopyMode = False
End Sub
Sub Umwandeln_in_feste_Werte_2()
'
' Umwandeln_in_feste_Werte Makro
' Dieses Makro macht aus Formeln feste Werte.
'
'
Range("A14").Value = Range("A14").Value
' kürzer
' Range("A14") = Range("A14")
End Sub
VBA/HTML - CodeConverter für Office-Foren, AddIn für Excel/Word 2000-2013 - komplett in VBA geschrieben von Lukas Mosimann. Projektbetreuung durch mumpel
Code erstellt und getestet in Office 15
und das ".Value" kannst Du auch noch weglassen.
Hi
hier zum Ersetzen und Setzen der Formeln, passend für dein gepostetes Beispiel
Code:
Sub SetzeFormeln()
With Worksheets("Mai 2015")
.Range("A5:A5").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF('aktive Mitglieder'!$H$4:$H$63=""A"",ROW($1:$60)),ROW(A1))),"""")"
.Range("A5:A54").FillDown
.Range("A62").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF(('aktive Mitglieder'!$H$4:$H$63=""A"")*(TEXT('aktive Mitglieder'!$I$4:$I$63,""hh:mm """"Uhr"""""")=""12:30 Uhr""),ROW($1:$60)),ROW(A1))),"""")"
.Range("A62:A111").FillDown
.Range("A117").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF(('aktive Mitglieder'!$H$4:$H$63=""A"")*(TEXT('aktive Mitglieder'!$I$4:$I$63,""hh:mm """"Uhr"""""")=""13:00 Uhr""),ROW($1:$60)),ROW(A1))),"""")"
.Range("A117:A166").FillDown
.Range("A172").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF(('aktive Mitglieder'!$H$4:$H$63=""A"")*(TEXT('aktive Mitglieder'!$J$4:$J$63,""hh:mm """"Uhr"""""")=""12:30 Uhr""),ROW($1:$60)),ROW(A1))),"""")"
.Range("A172:A221").FillDown
.Range("A227").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF(('aktive Mitglieder'!$H$4:$H$63=""A"")*(TEXT('aktive Mitglieder'!$J$4:$J$63,""hh:mm """"Uhr"""""")=""13:00 Uhr""),ROW($1:$60)),ROW(A1))),"""")"
.Range("A227:A276").FillDown
End With
End Sub
Sub ErsetzeFormeln()
With Worksheets("Mai 2015")
.Range("A5:A54").Value = .Range("A5:A54").Value
.Range("A62:A111").Value = .Range("A62:A111").Value
.Range("A117:A166").Value = .Range("A117:A166").Value
.Range("A172:A221").Value = .Range("A172:A221").Value
.Range("A227:A276").Value = .Range("A227:A276").Value
End With
End Sub
(16.05.2015, 09:29)Rabe schrieb: [ -> ]und das ".Value" kannst Du auch noch weglassen.
ja man kann, man sollte aber nicht, es ist mit .Value einfach 'sauberer' programmiert
Hi,
in AH steht ja ein Bezugsfehler, was sollte da statt #BEZUG! in der Formel stehen?
Hi Winny,
(16.05.2015, 09:40)Winny schrieb: [ -> ]ja man kann, man sollte aber nicht, es ist mit .Value einfach 'sauberer' programmiert
ja, klar, drum habe ich ja auch beides drin.
Hallo,
Danke funktioniert. Habe es wie von euch beschrieben gemacht, einfach Makro - Aufzeichnung genutzt und dann das was man nicht braucht entfernt. Dachte nur es gäbe einen einfachen Befehl der komplett auf einem Tabellelblatt alle Formeln gegen die Werte tauscht.
Danke euch. jetzt passt die Tabelle und ich bin zufrieden. :18:
Ralf,
der Bezug war noch aus anderen Tabellenblätter aber für dieses Problem nicht wichtig. Danke
Guten Morgen,
musste meine tabelle etwas umstellen sodass die suchmuster für die Monatstabellen anders erstellt werden mussten. Habe jetzt in der tabelle "aktive Mitglieder" nicht nach Uhrzeit sondern nach Auswahl über ein X festgelegt da es auch Kinder gibt die auf die Woche verteilt einmal 12:30 oder 13:00 Uhr essen. Somit müssen diese auch in der jeweiligen Gruppe bei beiden Tabellen 12:30 und 13:00 Uhr erscheinen. Wollte die Formeln von euch selbst umstellen aber bekomme das mal wieder nicht gebacken.
Könnte mir da bitte jemand Helfen ?
Muster ist angefügt
Hi
ich hab dir die Formeln geändert, schau auch auf die Formeln in Modul2 und den zusätzlichen Button Formeln