PDA

Vollständige Version anzeigen : runde Geburtstage über VBA ermitteln


mücke
24.02.2008, 14:02
Hallo Zusammen,

bräuchte mal kurz EURE Hilfe. :)

Möchte in einer Tabelle „Runde Geburtstage“ ausweisen.
Als Basis habe ich das…
Jahr (aktuell 2008) > steht in A1
Geburtsdatum (01.10.1971) > steht in/ab Spalte B3
Alter (37) > steht in/ab Spalte C3
Nun möchte ich in/ab Spalte D3 den nächsten „Runden Geburtstag“ ausweisen, hier 40.
Über ein Formel =C3+(10-REST(C3;10)) bekomme ich es auch hin. :grins:
Würde das Ergebnis aber viel lieber über eine saubere VBA Lösung erzielen.
Hat hier jemand eine Idee?

Über ein Feedback würde ich mich freuen.

Gruß Mücke

ransi
24.02.2008, 15:01
HAllo Mücke

Hier hast du mal einen Ansatz:
<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:98px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Verdana; text-align:right; ">25.02.1956</td><td >&nbsp;</td><td style="font-family:Verdana; text-align:right; ">25.02.2016</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D3</td><td >=naechster_runder(B3)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Dazu gehört dieser Code in einem Modul:
<nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" ><span style="color:#008000"; >' **********************************************************************</span><br /><span style="color:#008000"; >' Modul: Modul1 Typ: Allgemeines Modul</span><br /><span style="color:#008000"; >' **********************************************************************</span><br /><br /><span style="color:#000080"; >Option</span> <span style="color:#000080"; >Explicit</span><br /><br /><br /><b><span style="color:#000080"; >Public</span> <span style="color:#000080"; >Function</span> naechster_runder(s)</b><br /><span style="color:#000080"; >Dim</span> J<br />J = Year(s)<br /><span style="color:#000080"; >If</span> J &gt; Year(Now) <span style="color:#000080"; >Then</span> <span style="color:#000080"; >Exit</span> <span style="color:#000080"; >Function</span><br /><span style="color:#000080"; >Do</span><br />&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#008000"; >'J = J + 5</span><br />&nbsp;&nbsp;&nbsp;&nbsp;J = J + 10<br />&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#000080"; >If</span> J &gt;= Year(Now) <span style="color:#000080"; >Then</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;naechster_runder = DateSerial(J, Month(s), Day(s))<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#000080"; >Exit</span> <span style="color:#000080"; >Function</span><br />&nbsp;&nbsp;&nbsp;&nbsp;<span style="color:#000080"; >End</span> <span style="color:#000080"; >If</span><br /><span style="color:#000080"; >Loop</span><br /><b><span style="color:#000080"; >End</span> <span style="color:#000080"; >Function</span></b><br /></span></nobr>

ransi

Klaus-Dieter
24.02.2008, 15:05
Hallo Mücke,

versuche es mal damit:

Option Explicit

Sub rundeGeb()
Dim intZeile As Integer
For intZeile = 2 To 20
If (Year(Now) - Year(Cells(intZeile, 1))) Mod 10 = 0 Then
Cells(intZeile, 2) = Year(Now) - Year(Cells(intZeile, 1))
End If
Next intZeile
End Sub

Das Ergebnis:

<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">16.05.76</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">27.01.80</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">23.08.85</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">25.03.98</td><td style="text-align:right; ">10</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">17.08.81</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">12.10.78</td><td style="text-align:right; ">30</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">09.11.74</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">28.04.82</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">25.11.82</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">25.01.74</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">04.06.74</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">01.02.87</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">19.12.86</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">20.07.81</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">06.09.90</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">14.12.88</td><td style="text-align:right; ">20</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">20.03.73</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">27.12.83</td><td >&nbsp;</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">17.07.90</td><td >&nbsp;</td></tr></table>

mücke
24.02.2008, 16:41
Hallo ransi,
hallo Klaus-Dieter,

erstmal vielen Dank für die Lösungsvorschläge.
Doch leider sind die Ergebnisse (alle supi :) ) nicht ganz das was ich erreichen wollte.
Zur besseren Erläuterung hab ich die "Formel-Lösung" mal als Muster dargestellt.
Die Ergebnisse in den Spalten C - F sollen alle über/durch VBA errechnet werden.
<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:83px;" /><col style="width:96px;" /><col style="width:80px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-family:Arial; font-size:12pt; text-align:left; ">Jahr 2008</td><td style="font-family:Arial; font-size:10pt; ">&nbsp;</td><td style="font-family:Arial; font-size:10pt; ">&nbsp;</td><td colspan="3" style="font-family:Arial; font-size:10pt; text-align:center; ">n&auml;chster runder Geburtstag</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-weight:bold; font-family:Arial; font-size:10pt; ">Name</td><td style="font-weight:bold; font-family:Arial; font-size:10pt; text-align:center; ">Geburtsdatum</td><td style="font-weight:bold; font-family:Arial; font-size:10pt; text-align:center; ">Alter</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">mit</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">in</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">Jahr</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Arial; font-size:10pt; ">Beispiel 1</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">01.01.1987</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">21</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">30</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">9</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">2017</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Beispiel 2</td><td style="text-align:center; ">15.10.1978</td><td style="text-align:center; ">30</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">40</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">10</td><td style="font-family:Arial; font-size:10pt; text-align:center; ">2018</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D3</td><td >=C3+(10-REST<span style=' color:008000; '>(C3;10)</span>)</td></tr><tr><td >E3</td><td >=D3-C3</td></tr><tr><td >F3</td><td >=$A$1+E3</td></tr><tr><td >D4</td><td >=C4+(10-REST<span style=' color:008000; '>(C4;10)</span>)</td></tr><tr><td >E4</td><td >=D4-C4</td></tr><tr><td >F4</td><td >=$A$1+E4</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Habt IHR noch einen anderen Lösungsvorschlag ?

Gruß Mücke

mücke
24.02.2008, 19:20
Hallo,

wollte nur kurz posten,
habe einen Lösungsweg über VBA gefunden. :D
Schönes Wochenende.

Gruß Mücke

<b>Geburtstage</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:110px;" /><col style="width:96px;" /><col style="width:89px;" /><col style="width:61px;" /><col style="width:61px;" /><col style="width:61px;" /><col style="width:61px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:40px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-size:12pt; text-align:left; ">Jahr 2008</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td colspan="3" style="font-weight:bold; text-align:center; ">Runde Geburtstage</td></tr><tr style="height:26px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-weight:bold; ">Name</td><td colspan="2" style="font-weight:bold; text-align:center; ">Geburts- Datum / Monat</td><td style="font-weight:bold; text-align:center; ">Alter</td><td style="font-weight:bold; text-align:center; ">Alter</td><td style="font-weight:bold; font-family:Calibri; text-align:center; ">?</td><td style="font-weight:bold; text-align:center; ">Jahr</td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Muster</td><td style="text-align:center; ">24.02.1971</td><td style="text-align:left; ">Februar</td><td style="text-align:center; ">37</td><td style="text-align:center; ">40</td><td style="text-align:center; ">3</td><td style="text-align:center; ">2011</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Option Explicit

Dim efz As Integer 'erste freie Zelle
Dim J As Integer 'Jahr
Dim m As String 'Monat
Dim rg As Long 'Runde Geburtstage
Dim a As Long 'Alter
Dim i As Long 'Zähler

Private Sub CBut_übernahme_Click()

J = Sheets("Geburtstage").Cells(1, 1)
m = Format(DateValue(txt_GebDatum), "mmmm")
a = J - Year(DateValue(txt_GebDatum))

For i = 10 To 110 Step 10
If a < i Then
rg = i
i = 110
End If
Next i

efz = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(efz, 1) = txt_Name
Cells(efz, 2) = Format(DateValue(txt_GebDatum), "dd/mm/yyyy")
Cells(efz, 3) = m
Cells(efz, 4) = J - Year(DateValue(txt_GebDatum))
Cells(efz, 5) = rg
Cells(efz, 6) = rg - a
Cells(efz, 7) = J + (rg - a)
txt_Name = ""
txt_GebDatum = ""

End Sub