loop and some more

[alvin Kuiper]

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

 

[Joel]

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]

Hi joel

This just give me a loop there never stop?

Alvin


"Joel" skrev:

 

[Patrick Molloy]

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

 

[Joel]

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]

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:

 

[Joel]

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