PDA

View Full Version : Solved: Data from userform to sheet (dynamic)


enfantter
11-08-2007, 02:54 AM
Hey all,
I have a sheet which keeps on giving me problems...
I have the following code which generates some lines which assignments. There is a reference to a cell (cell(2, 9)) which shows this year. That means that i from the userform can create the assignments that have to be done this year (now 2007, actually this is 12 replications of the same assignment). What I would like to do is that if an assignment is create (fx) now i would like to enter values for the coming year, that is the two remaining months, but then jan, feb. march, etc should be 2008.
Does anybody se any simple way to come around this???


ElseIf nyfrek = 12 Then
'M?nedlige opgaver
If nyfrek = 12 Then
With Worksheets("Indtastning af ny opgave")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(lastrow + 1, "A").Value = nyTxt.Text
.Cells(lastrow + 1, "B").Value = nycmbans.Text
.Cells(lastrow + 1, "C").Value = nycmbmod.Text
.Cells(lastrow + 1, "D").Value = nycmbkar.Text
.Cells(lastrow + 1, "E").Value = nydagnr.Text
End With
With Worksheets("Struktur")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(lastrow + 1, "A").Value = nyTxt.Text
.Cells(lastrow + 1, "B").Value = nycmbans.Text
.Cells(lastrow + 1, "C").Value = nycmbmod.Text
.Cells(lastrow + 1, "D").Value = nycmbkar.Text
.Cells(lastrow + 1, "E").Value = Nyantalpers.Text
.Cells(lastrow + 1, "F").Value = nyvar.Text
.Cells(lastrow + 1, "G").Value = nyfrek.Text
.Cells(lastrow + 1, "H").Value = 1
.Cells(lastrow + 1, "I").Value = "jan"
.Cells(lastrow + 1, "J").Value = nydagnr.Text
.Cells(lastrow + 1, "K").Value = Workday(DateSerial(Cells(2, 9).Value, 0 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 1, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 0 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 2, "A").Value = nyTxt.Text
.Cells(lastrow + 2, "B").Value = nycmbans.Text
.Cells(lastrow + 2, "C").Value = nycmbmod.Text
.Cells(lastrow + 2, "D").Value = nycmbkar.Text
.Cells(lastrow + 2, "E").Value = Nyantalpers.Text
.Cells(lastrow + 2, "F").Value = nyvar.Text
.Cells(lastrow + 2, "G").Value = nyfrek.Text
.Cells(lastrow + 2, "H").Value = 2
.Cells(lastrow + 2, "I").Value = "feb"
.Cells(lastrow + 2, "J").Value = nydagnr.Text
.Cells(lastrow + 2, "K").Value = Workday(DateSerial(Cells(2, 9), 1 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 2, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 1 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)


.Cells(lastrow + 3, "A").Value = nyTxt.Text
.Cells(lastrow + 3, "B").Value = nycmbans.Text
.Cells(lastrow + 3, "C").Value = nycmbmod.Text
.Cells(lastrow + 3, "D").Value = nycmbkar.Text
.Cells(lastrow + 3, "E").Value = Nyantalpers.Text
.Cells(lastrow + 3, "F").Value = nyvar.Text
.Cells(lastrow + 3, "G").Value = nyfrek.Text
.Cells(lastrow + 3, "H").Value = 3
.Cells(lastrow + 3, "I").Value = "mar"
.Cells(lastrow + 3, "J").Value = nydagnr.Text
.Cells(lastrow + 3, "K").Value = Workday(DateSerial(Cells(2, 9), 2 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 3, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 2 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 4, "A").Value = nyTxt.Text
.Cells(lastrow + 4, "B").Value = nycmbans.Text
.Cells(lastrow + 4, "C").Value = nycmbmod.Text
.Cells(lastrow + 4, "D").Value = nycmbkar.Text
.Cells(lastrow + 4, "E").Value = Nyantalpers.Text
.Cells(lastrow + 4, "F").Value = nyvar.Text
.Cells(lastrow + 4, "G").Value = nyfrek.Text
.Cells(lastrow + 4, "H").Value = 4
.Cells(lastrow + 4, "I").Value = "apr"
.Cells(lastrow + 4, "J").Value = nydagnr.Text
.Cells(lastrow + 4, "K").Value = Workday(DateSerial(Cells(2, 9), 3 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 4, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 3 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)


.Cells(lastrow + 5, "A").Value = nyTxt.Text
.Cells(lastrow + 5, "B").Value = nycmbans.Text
.Cells(lastrow + 5, "C").Value = nycmbmod.Text
.Cells(lastrow + 5, "D").Value = nycmbkar.Text
.Cells(lastrow + 5, "E").Value = Nyantalpers.Text
.Cells(lastrow + 5, "F").Value = nyvar.Text
.Cells(lastrow + 5, "G").Value = nyfrek.Text
.Cells(lastrow + 5, "H").Value = 5
.Cells(lastrow + 5, "I").Value = "maj"
.Cells(lastrow + 5, "J").Value = nydagnr.Text
.Cells(lastrow + 5, "K").Value = Workday(DateSerial(Cells(2, 9), 4 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 5, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 4 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 6, "A").Value = nyTxt.Text
.Cells(lastrow + 6, "B").Value = nycmbans.Text
.Cells(lastrow + 6, "C").Value = nycmbmod.Text
.Cells(lastrow + 6, "D").Value = nycmbkar.Text
.Cells(lastrow + 6, "E").Value = Nyantalpers.Text
.Cells(lastrow + 6, "F").Value = nyvar.Text
.Cells(lastrow + 6, "G").Value = nyfrek.Text
.Cells(lastrow + 6, "H").Value = 6
.Cells(lastrow + 6, "I").Value = "jun"
.Cells(lastrow + 6, "J").Value = nydagnr.Text
.Cells(lastrow + 6, "K").Value = Workday(DateSerial(Cells(2, 9), 5 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 6, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 5 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 7, "A").Value = nyTxt.Text
.Cells(lastrow + 7, "B").Value = nycmbans.Text
.Cells(lastrow + 7, "C").Value = nycmbmod.Text
.Cells(lastrow + 7, "D").Value = nycmbkar.Text
.Cells(lastrow + 7, "E").Value = Nyantalpers.Text
.Cells(lastrow + 7, "F").Value = nyvar.Text
.Cells(lastrow + 7, "G").Value = nyfrek.Text
.Cells(lastrow + 7, "H").Value = 7
.Cells(lastrow + 7, "I").Value = "jul"
.Cells(lastrow + 7, "J").Value = nydagnr.Text
.Cells(lastrow + 7, "K").Value = Workday(DateSerial(Cells(2, 9), 6 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 7, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 6 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 8, "A").Value = nyTxt.Text
.Cells(lastrow + 8, "B").Value = nycmbans.Text
.Cells(lastrow + 8, "C").Value = nycmbmod.Text
.Cells(lastrow + 8, "D").Value = nycmbkar.Text
.Cells(lastrow + 8, "E").Value = Nyantalpers.Text
.Cells(lastrow + 8, "F").Value = nyvar.Text
.Cells(lastrow + 8, "G").Value = nyfrek.Text
.Cells(lastrow + 8, "H").Value = 8
.Cells(lastrow + 8, "I").Value = "aug"
.Cells(lastrow + 8, "J").Value = nydagnr.Text
.Cells(lastrow + 8, "K").Value = Workday(DateSerial(Cells(2, 9), 7 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 8, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 7 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 9, "A").Value = nyTxt.Text
.Cells(lastrow + 9, "B").Value = nycmbans.Text
.Cells(lastrow + 9, "C").Value = nycmbmod.Text
.Cells(lastrow + 9, "D").Value = nycmbkar.Text
.Cells(lastrow + 9, "E").Value = Nyantalpers.Text
.Cells(lastrow + 9, "F").Value = nyvar.Text
.Cells(lastrow + 9, "G").Value = nyfrek.Text
.Cells(lastrow + 9, "H").Value = 9
.Cells(lastrow + 9, "I").Value = "sep"
.Cells(lastrow + 9, "J").Value = nydagnr.Text
.Cells(lastrow + 9, "K").Value = Workday(DateSerial(Cells(2, 9), 8 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 9, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 8 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 10, "A").Value = nyTxt.Text
.Cells(lastrow + 10, "B").Value = nycmbans.Text
.Cells(lastrow + 10, "C").Value = nycmbmod.Text
.Cells(lastrow + 10, "D").Value = nycmbkar.Text
.Cells(lastrow + 10, "E").Value = Nyantalpers.Text
.Cells(lastrow + 10, "F").Value = nyvar.Text
.Cells(lastrow + 10, "G").Value = nyfrek.Text
.Cells(lastrow + 10, "H").Value = 10
.Cells(lastrow + 10, "I").Value = "okt"
.Cells(lastrow + 10, "J").Value = nydagnr.Text
.Cells(lastrow + 10, "K").Value = Workday(DateSerial(Cells(2, 9), 9 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 10, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 9 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 11, "A").Value = nyTxt.Text
.Cells(lastrow + 11, "B").Value = nycmbans.Text
.Cells(lastrow + 11, "C").Value = nycmbmod.Text
.Cells(lastrow + 11, "D").Value = nycmbkar.Text
.Cells(lastrow + 11, "E").Value = Nyantalpers.Text
.Cells(lastrow + 11, "F").Value = nyvar.Text
.Cells(lastrow + 11, "G").Value = nyfrek.Text
.Cells(lastrow + 11, "H").Value = 11
.Cells(lastrow + 11, "I").Value = "nov"
.Cells(lastrow + 11, "J").Value = nydagnr.Text
.Cells(lastrow + 11, "K").Value = Workday(DateSerial(Cells(2, 9), 10 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 11, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 10 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 12, "A").Value = nyTxt.Text
.Cells(lastrow + 12, "B").Value = nycmbans.Text
.Cells(lastrow + 12, "C").Value = nycmbmod.Text
.Cells(lastrow + 12, "D").Value = nycmbkar.Text
.Cells(lastrow + 12, "E").Value = Nyantalpers.Text
.Cells(lastrow + 12, "F").Value = nyvar.Text
.Cells(lastrow + 12, "G").Value = nyfrek.Text
.Cells(lastrow + 12, "H").Value = 12
.Cells(lastrow + 12, "I").Value = "dec"
.Cells(lastrow + 12, "J").Value = nydagnr.Text
.Cells(lastrow + 12, "K").Value = Workday(DateSerial(Cells(2, 9), 11 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 12, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 11 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)
End With
End If
figment
11-08-2007, 06:21 AM
being that every time you call on cell(2,9), you pass it to the Workday() function, be vary helpfull if you posted this function as well. with out that i realy cant help that much. also the second if statment is redundent, for the elseif already checks to make sure nyfrek = 12
enfantter
11-09-2007, 06:41 AM
Ok - i realize that i might have to specify my question slightly:

cell(2,9) is this year and workday finds the number in a month (excl. weekends). Workday has three arguments (year, month, day). In a userform i enter the two later ones, and the one that i is giving me problems is the year. I want to create 12 entries, but actually imnot interested in creating entries for jan 07 if we are now in nov 07, then i would rather do jan 08

Is this more clear?!
enfantter
11-09-2007, 06:42 AM
What
enfantter
11-09-2007, 06:45 AM
what i want to do is something like this i guess,

Workday(DateSerial(Cells(2, 9), 10 + val(nystart.Value), 0), val(nydagnr.Value)) if date > today
else
Workday(DateSerial(Cells(2, 9) + 1, 10 + val(nystart.Value), 0), val(nydagnr.Value)) if date > today
figment
11-09-2007, 08:31 AM
well with out seeing you code i can help you impliment it, but within your Workday() function. once you have made the date you can compare it to the curent date using the system varabile "Date" which is always set to the current date. the functions month() day() and year() would probibly help you as well.

as and example once you have found your date_in_january you could compare Year(date_in_january) to Year(Date) and find if you need to index the date_in_january by one year if it is already set to the current year.

if that made any sence i hope it helps.
enfantter
11-12-2007, 12:21 AM
Ok, i might specify my problem.
I guess i dont know how to compare the dates on the right hand side of the equations.

I dont know whether the rest of the code is useful, but here it is...
Private Sub cmdnyopguge_click()

Worksheets("Struktur").Cells(3, 1) = Year(today)
If nyTxt.Value = blank Then
MsgBox ("Opgavenavn skal indtastes for at kunne oprette en ny opgave")
End If
If nydagnr.Value = blank Then
MsgBox ("Startdato for opgave skal indtastes for at kunne oprette en ny opgave")
End If
If nystart.Value = blank Then
MsgBox ("Varighed af opgaven skal indtastes for at kunne oprette en ny opgave")
End If

ElseIf nyfrek = 52 Then
'M?nedlige opgaver
If nyfrek = 52 Then
With Worksheets("Indtastning af ny opgave")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(lastrow + 1, "A").Value = nyTxt.Text
.Cells(lastrow + 1, "B").Value = nycmbans.Text
.Cells(lastrow + 1, "C").Value = nycmbmod.Text
.Cells(lastrow + 1, "D").Value = nycmbkar.Text
.Cells(lastrow + 1, "E").Value = nydagnr.Text
End With
With Worksheets("Struktur")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(lastrow + 1, "A").Value = nyTxt.Text
.Cells(lastrow + 1, "B").Value = nycmbans.Text
.Cells(lastrow + 1, "C").Value = nycmbmod.Text
.Cells(lastrow + 1, "D").Value = nycmbkar.Text
.Cells(lastrow + 1, "E").Value = Nyantalpers.Text
.Cells(lastrow + 1, "F").Value = nyvar.Text
.Cells(lastrow + 1, "G").Value = nyfrek.Text
.Cells(lastrow + 1, "H").Value = 1
.Cells(lastrow + 1, "I").Value = "jan"
.Cells(lastrow + 1, "J").Value = nydagnr.Text
.Cells(lastrow + 1, "K").Value = Workday(DateSerial(Cells(2, 9).Value, 0 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 1, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 0 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 2, "A").Value = nyTxt.Text
.Cells(lastrow + 2, "B").Value = nycmbans.Text
.Cells(lastrow + 2, "C").Value = nycmbmod.Text
.Cells(lastrow + 2, "D").Value = nycmbkar.Text
.Cells(lastrow + 2, "E").Value = Nyantalpers.Text
.Cells(lastrow + 2, "F").Value = nyvar.Text
.Cells(lastrow + 2, "G").Value = nyfrek.Text
.Cells(lastrow + 2, "H").Value = 2
.Cells(lastrow + 2, "I").Value = "feb"
.Cells(lastrow + 2, "J").Value = nydagnr.Text
.Cells(lastrow + 2, "K").Value = Workday(DateSerial(Cells(2, 9), 1 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 2, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 1 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)


.Cells(lastrow + 3, "A").Value = nyTxt.Text
.Cells(lastrow + 3, "B").Value = nycmbans.Text
.Cells(lastrow + 3, "C").Value = nycmbmod.Text
.Cells(lastrow + 3, "D").Value = nycmbkar.Text
.Cells(lastrow + 3, "E").Value = Nyantalpers.Text
.Cells(lastrow + 3, "F").Value = nyvar.Text
.Cells(lastrow + 3, "G").Value = nyfrek.Text
.Cells(lastrow + 3, "H").Value = 3
.Cells(lastrow + 3, "I").Value = "mar"
.Cells(lastrow + 3, "J").Value = nydagnr.Text
.Cells(lastrow + 3, "K").Value = Workday(DateSerial(Cells(2, 9), 2 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 3, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 2 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 4, "A").Value = nyTxt.Text
.Cells(lastrow + 4, "B").Value = nycmbans.Text
.Cells(lastrow + 4, "C").Value = nycmbmod.Text
.Cells(lastrow + 4, "D").Value = nycmbkar.Text
.Cells(lastrow + 4, "E").Value = Nyantalpers.Text
.Cells(lastrow + 4, "F").Value = nyvar.Text
.Cells(lastrow + 4, "G").Value = nyfrek.Text
.Cells(lastrow + 4, "H").Value = 4
.Cells(lastrow + 4, "I").Value = "apr"
.Cells(lastrow + 4, "J").Value = nydagnr.Text
.Cells(lastrow + 4, "K").Value = Workday(DateSerial(Cells(2, 9), 3 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 4, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 3 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)


.Cells(lastrow + 5, "A").Value = nyTxt.Text
.Cells(lastrow + 5, "B").Value = nycmbans.Text
.Cells(lastrow + 5, "C").Value = nycmbmod.Text
.Cells(lastrow + 5, "D").Value = nycmbkar.Text
.Cells(lastrow + 5, "E").Value = Nyantalpers.Text
.Cells(lastrow + 5, "F").Value = nyvar.Text
.Cells(lastrow + 5, "G").Value = nyfrek.Text
.Cells(lastrow + 5, "H").Value = 5
.Cells(lastrow + 5, "I").Value = "maj"
.Cells(lastrow + 5, "J").Value = nydagnr.Text
.Cells(lastrow + 5, "K").Value = Workday(DateSerial(Cells(2, 9), 4 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 5, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 4 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 6, "A").Value = nyTxt.Text
.Cells(lastrow + 6, "B").Value = nycmbans.Text
.Cells(lastrow + 6, "C").Value = nycmbmod.Text
.Cells(lastrow + 6, "D").Value = nycmbkar.Text
.Cells(lastrow + 6, "E").Value = Nyantalpers.Text
.Cells(lastrow + 6, "F").Value = nyvar.Text
.Cells(lastrow + 6, "G").Value = nyfrek.Text
.Cells(lastrow + 6, "H").Value = 6
.Cells(lastrow + 6, "I").Value = "jun"
.Cells(lastrow + 6, "J").Value = nydagnr.Text
.Cells(lastrow + 6, "K").Value = Workday(DateSerial(Cells(2, 9), 5 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 6, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 5 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 7, "A").Value = nyTxt.Text
.Cells(lastrow + 7, "B").Value = nycmbans.Text
.Cells(lastrow + 7, "C").Value = nycmbmod.Text
.Cells(lastrow + 7, "D").Value = nycmbkar.Text
.Cells(lastrow + 7, "E").Value = Nyantalpers.Text
.Cells(lastrow + 7, "F").Value = nyvar.Text
.Cells(lastrow + 7, "G").Value = nyfrek.Text
.Cells(lastrow + 7, "H").Value = 7
.Cells(lastrow + 7, "I").Value = "jul"
.Cells(lastrow + 7, "J").Value = nydagnr.Text
.Cells(lastrow + 7, "K").Value = Workday(DateSerial(Cells(2, 9), 6 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 7, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 6 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 8, "A").Value = nyTxt.Text
.Cells(lastrow + 8, "B").Value = nycmbans.Text
.Cells(lastrow + 8, "C").Value = nycmbmod.Text
.Cells(lastrow + 8, "D").Value = nycmbkar.Text
.Cells(lastrow + 8, "E").Value = Nyantalpers.Text
.Cells(lastrow + 8, "F").Value = nyvar.Text
.Cells(lastrow + 8, "G").Value = nyfrek.Text
.Cells(lastrow + 8, "H").Value = 8
.Cells(lastrow + 8, "I").Value = "aug"
.Cells(lastrow + 8, "J").Value = nydagnr.Text
.Cells(lastrow + 8, "K").Value = Workday(DateSerial(Cells(2, 9), 7 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 8, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 7 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 9, "A").Value = nyTxt.Text
.Cells(lastrow + 9, "B").Value = nycmbans.Text
.Cells(lastrow + 9, "C").Value = nycmbmod.Text
.Cells(lastrow + 9, "D").Value = nycmbkar.Text
.Cells(lastrow + 9, "E").Value = Nyantalpers.Text
.Cells(lastrow + 9, "F").Value = nyvar.Text
.Cells(lastrow + 9, "G").Value = nyfrek.Text
.Cells(lastrow + 9, "H").Value = 9
.Cells(lastrow + 9, "I").Value = "sep"
.Cells(lastrow + 9, "J").Value = nydagnr.Text
.Cells(lastrow + 9, "K").Value = Workday(DateSerial(Cells(2, 9), 8 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 9, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 8 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 10, "A").Value = nyTxt.Text
.Cells(lastrow + 10, "B").Value = nycmbans.Text
.Cells(lastrow + 10, "C").Value = nycmbmod.Text
.Cells(lastrow + 10, "D").Value = nycmbkar.Text
.Cells(lastrow + 10, "E").Value = Nyantalpers.Text
.Cells(lastrow + 10, "F").Value = nyvar.Text
.Cells(lastrow + 10, "G").Value = nyfrek.Text
.Cells(lastrow + 10, "H").Value = 10
.Cells(lastrow + 10, "I").Value = "okt"
.Cells(lastrow + 10, "J").Value = nydagnr.Text
.Cells(lastrow + 10, "K").Value = Workday(DateSerial(Cells(2, 9), 9 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 10, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 9 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 11, "A").Value = nyTxt.Text
.Cells(lastrow + 11, "B").Value = nycmbans.Text
.Cells(lastrow + 11, "C").Value = nycmbmod.Text
.Cells(lastrow + 11, "D").Value = nycmbkar.Text
.Cells(lastrow + 11, "E").Value = Nyantalpers.Text
.Cells(lastrow + 11, "F").Value = nyvar.Text
.Cells(lastrow + 11, "G").Value = nyfrek.Text
.Cells(lastrow + 11, "H").Value = 11
.Cells(lastrow + 11, "I").Value = "nov"
.Cells(lastrow + 11, "J").Value = nydagnr.Text
.Cells(lastrow + 11, "K").Value = Workday(DateSerial(Cells(2, 9), 10 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 11, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 10 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)

.Cells(lastrow + 12, "A").Value = nyTxt.Text
.Cells(lastrow + 12, "B").Value = nycmbans.Text
.Cells(lastrow + 12, "C").Value = nycmbmod.Text
.Cells(lastrow + 12, "D").Value = nycmbkar.Text
.Cells(lastrow + 12, "E").Value = Nyantalpers.Text
.Cells(lastrow + 12, "F").Value = nyvar.Text
.Cells(lastrow + 12, "G").Value = nyfrek.Text
.Cells(lastrow + 12, "H").Value = 12
.Cells(lastrow + 12, "I").Value = "dec"
.Cells(lastrow + 12, "J").Value = nydagnr.Text
.Cells(lastrow + 12, "K").Value = Workday(DateSerial(Cells(2, 9), 11 + val(nystart.Value), 0), val(nydagnr.Value))
.Cells(lastrow + 12, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, 11 + val(nystart.Value), 0), val(nydagnr.Value)), nyvar - 1)
End With
End If
End Sub
JimmyTheHand
11-12-2007, 04:30 AM
Hi

I'm not sure I quite grasp essence of the question, but here is what I suggest.

The code could (that is, should) be greatly shortened. All those repeating lines can be replaced by one loop like this:

Dim i As Long, Arr()

Arr() = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "okt", "nov", "dec")
For i = 1 To 12
.Cells(lastrow + i, "A").Value = nyTxt.Text
.Cells(lastrow + i, "B").Value = nycmbans.Text
.Cells(lastrow + i, "C").Value = nycmbmod.Text
.Cells(lastrow + i, "D").Value = nycmbkar.Text
.Cells(lastrow + i, "E").Value = Nyantalpers.Text
.Cells(lastrow + i, "F").Value = nyvar.Text
.Cells(lastrow + i, "G").Value = nyfrek.Text
.Cells(lastrow + i, "H").Value = i
.Cells(lastrow + i, "I").Value = Arr(i)
.Cells(lastrow + i, "J").Value = nydagnr.Text
.Cells(lastrow + i, "K").Value = Workday(DateSerial(Cells(2, 9), i - 1 + Val(nystart.Value), 0), Val(nydagnr.Value))
.Cells(lastrow + i, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, i - 1 + Val(nystart.Value), 0), Val(nydagnr.Value)), nyvar - 1)
Next


As for the problem with years, I recommend using this function:
Function WhichYear(ReferenceDate As Date, HowManyMonthsForward As Long) As Long
WhichYear = Year(ReferenceDate) + (Month(ReferenceDate) + HowManyMonthsForward - 1) \ 12
End Function

Jimmy
enfantter
11-12-2007, 06:47 AM
Ok, this really seems like a great help, should I then put the whichyear function instead of cells(2, 9) (which has the static input "2007")??
JimmyTheHand
11-12-2007, 07:51 AM
should I then put the whichyear function instead of cells(2, 9) (which has the static input "2007")??
In general, yes.
In particular, I don't know. It depends on what the purpose of these lines is:
.Cells(lastrow + i, "K").Value = Workday(DateSerial(Cells(2, 9), i - 1 + Val(nystart.Value), 0), Val(nydagnr.Value))
.Cells(lastrow + i, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, i - 1 + Val(nystart.Value), 0), Val(nydagnr.Value)), nyvar - 1) Maybe even another part of the code, bigger than Cells(2, 9), can be replaced by WhichYear, or another, cleverly designed fuction.

Jimmy
enfantter
11-12-2007, 08:07 AM
sorry, these lines transport a starting date (line 1) and an end date (line 2) to the sheet.
And it is here that i want it to transport a date with the suffix, 2008, if the date is before the current.
JimmyTheHand
11-12-2007, 08:27 AM
What are nystart.Value and nydagnr.Value?
enfantter
11-12-2007, 01:21 PM
Im trying to enter some entries.
These have a start date and an end date.
The startdate is generated from
.Cells(lastrow + i, "K").Value = Workday(DateSerial(Cells(2, 9), i - 1 + Val(nystart.Value), 0), Val(nydagnr.Value))

which is basically calculating the workday from (year (which is in cell (2,9)); month (which is nystart.value); and the day (eg the fifth) of the month (nydagnr.value))

hope this clarifies??
JimmyTheHand
11-12-2007, 11:47 PM
hope this clarifies??
Well, your goal is more clear, I suppose, but the code is all the more confusing. See my notes below.

1) In post #3 you said that the WorkDay function has 3 arguments: Year, Month and Day. However, in the code, there are only two, the red one and the blue one:
.Cells(lastrow + i, "K").Value = Workday(DateSerial(Cells(2, 9), i - 1 + Val(nystart.Value), 0), Val(nydagnr.Value))
2) The expression in red, used as first argument of Workday function, returns a full date, e.g. 2007. october 31., whereas you said, the first argument should be only a year number.

3) DateSerial function does modify the initial year and month, if the resulting date is over the end of the year or month. E.g.


DateSerial(2007, 11, 32) results in 2007.dec.02.
DateSerial(2007, 16, 32) results in 2008.may.02.


So, the red expression above feeds the Workday function with an argument that refers to the correct year, and if the Workday function works otherwise correctly, there should be no need for modifications there.

4) In my understanding, the Workday function returns a number, more precisely, it counts the working days in a given month. Then what is
Workday(Workday(arguments),arguments)
supposed to do???

5) In my understanding, the code is supposed to create assignments but the wrong year is displayed on them. The code you gave never displays the value of Cells(2, 9) directly. How do you know the year is wrong?


Jimmy
enfantter
11-13-2007, 01:21 PM
Im sorry - i did put it wrong ...

Workday has two arguements, one is a date, and the other is scalar(interger). Ex: in column "K" i want the start date of the assignment so in a userform i have entered the first month this assignment is going to run. Lets say that this is the 10th month. This means that the date serial (red part)will spit out (not actually shown anywhere) 1st of oktober 2007 (or actually the o th). In the userform is also enterred the workday upon which the assignment is carried out (lets say the fifth). This means that the workday function will count 5 workdays from the dateserial and spit out the 5th workday of oktober 2007. This assignment is replicated 12 times one for each month in a year. As it is now it does it for jan-dec 2007 whereas i would like it to do it for oct-dec 2007 and then be able to do the rest for jan-sep 2008. Eg im hoping to create a formula which can generate for one year forward instead of 12 times this year (or 2008)

The column "J" works the same way.

Hope that this clarifies a bit??!?!
JimmyTheHand
11-13-2007, 09:26 PM
Okay, I think I've got it now. It seems to me that the Workday function is wrong. I can't say where, because I would need to see its code to do that.

But I suggest that you run this little test, and compare the results of DateSerial and Workday functions:

Sub test_Workday
Dim i as Long
For i=1 To 12
Range("A" & i) = DateSerial(2007, 10 + i, 0)
Range("B" & i) = Workday(DateSerial(2007, 10 + i, 0), 5)
Next
End Sub

Is the Workday function your own development? Can you post its code? I'd like to see it.

Jimmy
enfantter
11-14-2007, 06:38 AM
Ok, lets see if we can get rid of the last part of misunderstandings:)

The workday function is not my own creation, but a feature in excel. It doesnt actually spit out the wrong date. Since the input is 2007, it will only create dates for assignments in this year (if the value in cell(2,9) was 2008, this would be the year). I was just hoping that it was possible to write additional code inside which would enable the function to distinquish whether the date has passed or not, since it doesnt make much sense to create a date for an assignment in the past (it would be more reasonable to then create all the assignments one year in advance)

Better??
JimmyTheHand
11-14-2007, 08:26 AM
Better??
No.
The input of Workday function is, at least in the posted code, never directly Cells(2, 9). So the input is not a constant 2007. The input is DateSerial(somethings), a function which, as shown by the little test routine in my latest post, returns dates in 2008 as well.

There must be something very obvious that I don't see. :dunno I'm starting to feel stupid. Can you post your workbook? It might help me to understand. (Of course, you should strip it of any confidential data first.)
figment
11-14-2007, 09:18 AM
i was reading back through this and i think i know what might be the problem. it looks like your working with the formated date rather then the numral date. if you can clarify a few things about your inputs it would probibly help. i appoligize if any of this is repeeting

1. the value of Cell(2,9) is it just a year ie. 2007 or is it 07 or is it the full date?

2. what format is the input nystart.Value? is it a number or is it a date? mostly what dose it look like. and dose it come from a user form or a cell range?

3. same question as abouve for nydagnr.value?

the reason i ask, is the way excel handles dates, what your asking is much easyer to do with the date number and let the excel GUI handle the formating, but it sounds like your trying to do the formating your self, rather then leting excel handle it.

again sorry if any of this is redundant.
enfantter
11-14-2007, 12:05 PM
@Jimmy

Workday calculates a start date. I know, that cell(2,9) is not direct input, put that cell is what controls the year which comes out. Its static, so what I want to do i gues is to replace this with some more ingenous function, but yes youre right, we might be missing each others point, ill post it tomorrow morning, when i get to that comp.

@figment
cell(2,9) is a number: 2007 (static)
Nystart.value is a number from userform (textbox) which corresponds to a month.
Nydagnr.value is a number from userform (textbox) which corresponds to the day no. where the assignment can begin.
figment
11-14-2007, 01:04 PM
i never did find the workday funcktion, so i was not able to test this but here is my attempt.

Dim i As Long, Arr()

Arr() = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "okt", "nov", "dec")
For i = 1 To 12
.Cells(lastrow + i, "A").Value = nyTxt.Text
.Cells(lastrow + i, "B").Value = nycmbans.Text
.Cells(lastrow + i, "C").Value = nycmbmod.Text
.Cells(lastrow + i, "D").Value = nycmbkar.Text
.Cells(lastrow + i, "E").Value = Nyantalpers.Text
.Cells(lastrow + i, "F").Value = nyvar.Text
.Cells(lastrow + i, "G").Value = nyfrek.Text
.Cells(lastrow + i, "H").Value = i
.Cells(lastrow + i, "I").Value = Arr(i)
.Cells(lastrow + i, "J").Value = nydagnr.Text
If i <= Month(Now()) Then
.Cells(lastrow + i, "K").Value = Workday(DateSerial(Cells(2, 9)+1, i - 1 + Val(nystart.Value) , 0), Val(nydagnr.Value))
.Cells(lastrow + i, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value+1, i - 1 + Val(nystart.Value) , 0), Val(nydagnr.Value)), nyvar - 1)
Else
.Cells(lastrow + i, "K").Value = Workday(DateSerial(Cells(2, 9), i - 1 + Val(nystart.Value), 0), Val(nydagnr.Value))
.Cells(lastrow + i, "L").Value = Workday(Workday(DateSerial(Cells(2, 9).Value, i - 1 + Val(nystart.Value), 0), Val(nydagnr.Value)), nyvar - 1)
End If
Next

i will take another look once you post the workbook
enfantter
11-15-2007, 12:22 AM
As promised - here youve got it.

go to sheet "Indtastning af ny opgave" press the button "ny opgave" here is where you create the assigment which is then generated on to the sheet "struktur".

The code of course is in the userform "tilf?jelse".
tell me if you need anything...
/jack
enfantter
11-15-2007, 12:51 AM
i guess its to big --
suggestions?!
JimmyTheHand
11-15-2007, 01:05 AM
i guess its to big --
suggestions?!

Try to compress it to zip?
enfantter
11-15-2007, 01:18 AM
Very nice ...
A bit slow this morning i guess;)

follow instructions above
JimmyTheHand
11-15-2007, 03:17 AM
Well I had some difficulties, first with opening the file, then with obtaining Workday function, and finally, with understanding the words on the form.
But then I could test the program. I might be wrong but I think it works perfectly well. I post the result of my trials, see the attached file.

However, I noticed that all cells in columns K and L contained formulas, when I opened the file. Those formulas used Cells(2, 9), that is G2, as a direct input parameter. E.g. in K332 the formula was
=WORKDAY(DATE($G$2,H332,0),J332)
And in these formulas there is no reference to the starting month, as if each assignment would start in January.

On the other hand, when I run the code, all new entries in column K and L were simple values, never formulas. Which I'm not surprised of. I would, however, be quite surprised to find that the same code puts a value for me, and a formula for you, into the same cell.

We are not much farther ahead than in the morning, I'm afraid.
Please tell me, in your posted workbook (Oversigt temp.xls), which cells you don't like.
Or, create a new assignment with the code, and post the workbook again, and tell me which cells you don't like.

Jimmy
enfantter
11-16-2007, 05:40 AM
@Jimmy

Yes, im sorry those formulas just served to show me how the formula should be formed.

@figment

Tnx! this is sort of simple and it is excactly what I wanted to do!