| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop and some more
Hi
I try to explain this very difficult task (for me) I have this in a shet (perioder) Here is 3 rows but there can bee up to 20 rows B C D E F G 39,22 39,22 02.01 28.02 02-01-2009 28-02-2009 47,17 47,17 01.03 31.10 01-03-2009 31-10-2009 44 44 01.11 31.12 01-11-2009 31-12-2009 then i have a userform with 2 fields as date (start and end) What i want now is To make a loop there goes from 1 to 20 If my start is = ( F and the row) and end is <= (G and the row) Then Take the value from B and copy it to another sheet = beregn But thats not all : If my start is 30-10 and my end is 02-11 Then i shall have 2 days with the value 39,22 and 2 days with the value 47,17 I have try this: For I = 1 To 20 If Priskalk.fradato.Value = Range("perioder!" & kl & I).Value And Priskalk.Tildato.Value <= Range("perioder!" & kl2 & I).Value Then For s = Priskalk.fradato.Value To Priskalk.Tildato.Value Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & tal).Value tal = tal + 1 Next End If If Range("perioder!" & kl & start).Value = "" Then Exit For End If Next Nothing happens ????????? Hope someone can help Alvin |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and some more
With Sheets("perioder")
OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = Date(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = Date(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets("beregner").Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi I try to explain this very difficult task (for me) I have this in a shet (perioder) Here is 3 rows but there can bee up to 20 rows B C D E F G 39,22 39,22 02.01 28.02 02-01-2009 28-02-2009 47,17 47,17 01.03 31.10 01-03-2009 31-10-2009 44 44 01.11 31.12 01-11-2009 31-12-2009 then i have a userform with 2 fields as date (start and end) What i want now is To make a loop there goes from 1 to 20 If my start is = ( F and the row) and end is <= (G and the row) Then Take the value from B and copy it to another sheet = beregn But thats not all : If my start is 30-10 and my end is 02-11 Then i shall have 2 days with the value 39,22 and 2 days with the value 47,17 I have try this: For I = 1 To 20 If Priskalk.fradato.Value = Range("perioder!" & kl & I).Value And Priskalk.Tildato.Value <= Range("perioder!" & kl2 & I).Value Then For s = Priskalk.fradato.Value To Priskalk.Tildato.Value Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & tal).Value tal = tal + 1 Next End If If Range("perioder!" & kl & start).Value = "" Then Exit For End If Next Nothing happens ????????? Hope someone can help Alvin |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and some more
Hi joel
This just give me a loop there never stop? Alvin "Joel" skrev: With Sheets("perioder") OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = Date(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = Date(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets("beregner").Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi I try to explain this very difficult task (for me) I have this in a shet (perioder) Here is 3 rows but there can bee up to 20 rows B C D E F G 39,22 39,22 02.01 28.02 02-01-2009 28-02-2009 47,17 47,17 01.03 31.10 01-03-2009 31-10-2009 44 44 01.11 31.12 01-11-2009 31-12-2009 then i have a userform with 2 fields as date (start and end) What i want now is To make a loop there goes from 1 to 20 If my start is = ( F and the row) and end is <= (G and the row) Then Take the value from B and copy it to another sheet = beregn But thats not all : If my start is 30-10 and my end is 02-11 Then i shall have 2 days with the value 39,22 and 2 days with the value 47,17 I have try this: For I = 1 To 20 If Priskalk.fradato.Value = Range("perioder!" & kl & I).Value And Priskalk.Tildato.Value <= Range("perioder!" & kl2 & I).Value Then For s = Priskalk.fradato.Value To Priskalk.Tildato.Value Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & tal).Value tal = tal + 1 Next End If If Range("perioder!" & kl & start).Value = "" Then Exit For End If Next Nothing happens ????????? Hope someone can help Alvin |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and some more
the loop counter is incremented as the last line of the DO...LOOP
OldRow = OldRow + 1 and the DO has the condition While OldRow <= 20 hence it cannot be an endless loop "alvin Kuiper" wrote in message ... Hi joel This just give me a loop there never stop? Alvin "Joel" skrev: With Sheets("perioder") OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = Date(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = Date(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets("beregner").Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi I try to explain this very difficult task (for me) I have this in a shet (perioder) Here is 3 rows but there can bee up to 20 rows B C D E F G 39,22 39,22 02.01 28.02 02-01-2009 28-02-2009 47,17 47,17 01.03 31.10 01-03-2009 31-10-2009 44 44 01.11 31.12 01-11-2009 31-12-2009 then i have a userform with 2 fields as date (start and end) What i want now is To make a loop there goes from 1 to 20 If my start is = ( F and the row) and end is <= (G and the row) Then Take the value from B and copy it to another sheet = beregn But thats not all : If my start is 30-10 and my end is 02-11 Then i shall have 2 days with the value 39,22 and 2 days with the value 47,17 I have try this: For I = 1 To 20 If Priskalk.fradato.Value = Range("perioder!" & kl & I).Value And Priskalk.Tildato.Value <= Range("perioder!" & kl2 & I).Value Then For s = Priskalk.fradato.Value To Priskalk.Tildato.Value Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & tal).Value tal = tal + 1 Next End If If Range("perioder!" & kl & start).Value = "" Then Exit For End If Next Nothing happens ????????? Hope someone can help Alvin |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and some more
I don't think the dates on the worksheet are dates instead strings. i
modified the code tohandle this problem. Look at this date 02-01-2009 If the date was a true date the zeroes wouldn't be infront of the 2 and 1. I'm in the USA and not sure if this is a difference in the international setting or a real problem so I modified the code so it will work with either a string or a real date. With Sheets("perioder") OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = DateValue(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) StartDate = Replace(StartDate, "-", "/") StartDate = DateValue(StartDate) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = DateValue(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) EndDate = Replace(EndDate, "-", "/") EndDate = DateValue(EndDate) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets(perioder).Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi joel This just give me a loop there never stop? Alvin "Joel" skrev: With Sheets("perioder") OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = Date(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = Date(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets("beregner").Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi I try to explain this very difficult task (for me) I have this in a shet (perioder) Here is 3 rows but there can bee up to 20 rows B C D E F G 39,22 39,22 02.01 28.02 02-01-2009 28-02-2009 47,17 47,17 01.03 31.10 01-03-2009 31-10-2009 44 44 01.11 31.12 01-11-2009 31-12-2009 then i have a userform with 2 fields as date (start and end) What i want now is To make a loop there goes from 1 to 20 If my start is = ( F and the row) and end is <= (G and the row) Then Take the value from B and copy it to another sheet = beregn But thats not all : If my start is 30-10 and my end is 02-11 Then i shall have 2 days with the value 39,22 and 2 days with the value 47,17 I have try this: For I = 1 To 20 If Priskalk.fradato.Value = Range("perioder!" & kl & I).Value And Priskalk.Tildato.Value <= Range("perioder!" & kl2 & I).Value Then For s = Priskalk.fradato.Value To Priskalk.Tildato.Value Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & tal).Value tal = tal + 1 Next End If If Range("perioder!" & kl & start).Value = "" Then Exit For End If Next Nothing happens ????????? Hope someone can help Alvin |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and some more
Hi joel
Now i get script out of range on: Sheets(perioder).Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value But i have this soloution there works Not so nice as yours but it works Dim s As Date Dim tal As Integer tal = 1 Dim start As Date Dim ende As Date start = DateAdd("d", 1, Priskalk.fradato.Value) ende = DateAdd("d", -1, Priskalk.Tildato.Value) Dim kl As String Dim I As Integer kl = "f" kl2 = "g" kl3 = "b" kk = "a" For I = 1 To 20 If start = CDate(Range("perioder!" & kl & I).Value) And start <= CDate(Range("perioder!" & kl2 & I).Value) Then For s = start To ende Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & I).Value tal = tal + 1 If s CDate(Range("perioder!" & kl2 & I).Value) Then start = s Exit For End If Next End If If Range("perioder!" & kl & I).Value = "" Then Exit For End If alvin Next "Joel" skrev: I don't think the dates on the worksheet are dates instead strings. i modified the code tohandle this problem. Look at this date 02-01-2009 If the date was a true date the zeroes wouldn't be infront of the 2 and 1. I'm in the USA and not sure if this is a difference in the international setting or a real problem so I modified the code so it will work with either a string or a real date. With Sheets("perioder") OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = DateValue(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) StartDate = Replace(StartDate, "-", "/") StartDate = DateValue(StartDate) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = DateValue(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) EndDate = Replace(EndDate, "-", "/") EndDate = DateValue(EndDate) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets(perioder).Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi joel This just give me a loop there never stop? Alvin "Joel" skrev: With Sheets("perioder") OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = Date(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = Date(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets("beregner").Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi I try to explain this very difficult task (for me) I have this in a shet (perioder) Here is 3 rows but there can bee up to 20 rows B C D E F G 39,22 39,22 02.01 28.02 02-01-2009 28-02-2009 47,17 47,17 01.03 31.10 01-03-2009 31-10-2009 44 44 01.11 31.12 01-11-2009 31-12-2009 then i have a userform with 2 fields as date (start and end) What i want now is To make a loop there goes from 1 to 20 If my start is = ( F and the row) and end is <= (G and the row) Then Take the value from B and copy it to another sheet = beregn But thats not all : If my start is 30-10 and my end is 02-11 Then i shall have 2 days with the value 39,22 and 2 days with the value 47,17 I have try this: For I = 1 To 20 If Priskalk.fradato.Value = Range("perioder!" & kl & I).Value And Priskalk.Tildato.Value <= Range("perioder!" & kl2 & I).Value Then For s = Priskalk.fradato.Value To Priskalk.Tildato.Value Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & tal).Value tal = tal + 1 Next End If If Range("perioder!" & kl & start).Value = "" Then Exit For End If Next Nothing happens ????????? Hope someone can help Alvin |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop and some more
I missed the double quotes around the sheet name
from Sheets(perioder).Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value to Sheets("perioder").Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value "alvin Kuiper" wrote: Hi joel Now i get script out of range on: Sheets(perioder).Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value But i have this soloution there works Not so nice as yours but it works Dim s As Date Dim tal As Integer tal = 1 Dim start As Date Dim ende As Date start = DateAdd("d", 1, Priskalk.fradato.Value) ende = DateAdd("d", -1, Priskalk.Tildato.Value) Dim kl As String Dim I As Integer kl = "f" kl2 = "g" kl3 = "b" kk = "a" For I = 1 To 20 If start = CDate(Range("perioder!" & kl & I).Value) And start <= CDate(Range("perioder!" & kl2 & I).Value) Then For s = start To ende Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & I).Value tal = tal + 1 If s CDate(Range("perioder!" & kl2 & I).Value) Then start = s Exit For End If Next End If If Range("perioder!" & kl & I).Value = "" Then Exit For End If alvin Next "Joel" skrev: I don't think the dates on the worksheet are dates instead strings. i modified the code tohandle this problem. Look at this date 02-01-2009 If the date was a true date the zeroes wouldn't be infront of the 2 and 1. I'm in the USA and not sure if this is a difference in the international setting or a real problem so I modified the code so it will work with either a string or a real date. With Sheets("perioder") OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = DateValue(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) StartDate = Replace(StartDate, "-", "/") StartDate = DateValue(StartDate) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = DateValue(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) EndDate = Replace(EndDate, "-", "/") EndDate = DateValue(EndDate) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets(perioder).Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi joel This just give me a loop there never stop? Alvin "Joel" skrev: With Sheets("perioder") OldRow = 1 NewRow = 1 Do While OldRow <= 20 FormStartDate = Date(Priskalk.fradato.Value) StartDate = .Range("F" & OldRow) If FormStartDate StartDate Then StartDate = FormStartDate End If FormEndDate = Date(Priskalk.Tildato.Value) EndDate = .Range("G" & OldRow) If FormEndDate EndDate Then EndDate = FormEndDate End If For MyDate = StartDate To EndDate Sheets("beregner").Range("A" & NewRow).Value = _ .Range("B" & NewRow).Value NewRow = NewRow + 1 Next MyDate OldRow = OldRow + 1 Loop End With "alvin Kuiper" wrote: Hi I try to explain this very difficult task (for me) I have this in a shet (perioder) Here is 3 rows but there can bee up to 20 rows B C D E F G 39,22 39,22 02.01 28.02 02-01-2009 28-02-2009 47,17 47,17 01.03 31.10 01-03-2009 31-10-2009 44 44 01.11 31.12 01-11-2009 31-12-2009 then i have a userform with 2 fields as date (start and end) What i want now is To make a loop there goes from 1 to 20 If my start is = ( F and the row) and end is <= (G and the row) Then Take the value from B and copy it to another sheet = beregn But thats not all : If my start is 30-10 and my end is 02-11 Then i shall have 2 days with the value 39,22 and 2 days with the value 47,17 I have try this: For I = 1 To 20 If Priskalk.fradato.Value = Range("perioder!" & kl & I).Value And Priskalk.Tildato.Value <= Range("perioder!" & kl2 & I).Value Then For s = Priskalk.fradato.Value To Priskalk.Tildato.Value Range("beregner!" & kk & tal).Value = Range("perioder!" & kl3 & tal).Value tal = tal + 1 Next End If If Range("perioder!" & kl & start).Value = "" Then Exit For End If Next Nothing happens ????????? Hope someone can help Alvin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| returning back to loop check condition without completing the loop | Excel Programming | |||
| Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
| Naming Worksheets - Loop within a loop issue | Excel Programming | |||
| Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
| Problem adding charts using Do-Loop Until loop | Excel Programming | |||
Linear Mode
