Hi

I want to be able to calculate a figure based on the no. of weeks between a
range of dates.

eg.
Records:
Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150

If the date to field is blank then today's date is to be used

At the moment I have the following:

startdate = "24/11/2003"
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * 120)

This works fine if the amount is always 120 and the start date is always
24/11/2003.
How can I change it so that
bal = the addition of No. of weeks between each date in the table * the
applicable amount?

The # of records will always be increasing - somehow I need to be able to
calculate each record separately

Thanks

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
if cell.offset(0,1) = "" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy


"Newbie" wrote in message
...
Hi

I want to be able to calculate a figure based on the no. of weeks between
a
range of dates.

eg.
Records:
Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150

If the date to field is blank then today's date is to be used

At the moment I have the following:

startdate = "24/11/2003"
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * 120)

This works fine if the amount is always 120 and the start date is always
24/11/2003.
How can I change it so that
bal = the addition of No. of weeks between each date in the table * the
applicable amount?

The # of records will always be increasing - somehow I need to be able to
calculate each record separately

Thanks

Thanks but the first if always returns true

I have the following:

Worksheets("Sheet1").Activate
For Each cell In Range("A1:A50")
If IsEmpty(cell) Then
startdate = Date
Else
startdate = cell
End If

My worksheet has data in row A1 and A2.

What am I doing wrong?
Thanks again

"Tom Ogilvy" wrote in message
...

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
if cell.offset(0,1) = "" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy


"Newbie" wrote in message
...
Hi

I want to be able to calculate a figure based on the no. of weeks
between
a
range of dates.

eg.
Records:
Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150

If the date to field is blank then today's date is to be used

At the moment I have the following:

startdate = "24/11/2003"
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * 120)

This works fine if the amount is always 120 and the start date is always
24/11/2003.
How can I change it so that
bal = the addition of No. of weeks between each date in the table * the
applicable amount?

The # of records will always be increasing - somehow I need to be able
to
calculate each record separately

Thanks

What your example showed was this:

Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150


Assuming Date From is in A1, that shows Data in A2:A4

I won't attempt to guess what your actual situation is. (receipt =
cells(6,3) ????)

I will amend the code to reflect what you originally said about any date
that is empty should be today's date (I only looked at column A)

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
If isempty(cell.offset(0,1)) then
EndDate = Date
Else
EndDate = cell.offset(0,2)
End if
if cell.offset(0,1) = "" and cell="" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, EndDate)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy



"Newbie" wrote in message
...
Thanks but the first if always returns true

I have the following:

Worksheets("Sheet1").Activate
For Each cell In Range("A1:A50")
If IsEmpty(cell) Then
startdate = Date
Else
startdate = cell
End If

My worksheet has data in row A1 and A2.

What am I doing wrong?
Thanks again

"Tom Ogilvy" wrote in message
...

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
if cell.offset(0,1) = "" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy


"Newbie" wrote in message
...
Hi

I want to be able to calculate a figure based on the no. of weeks
between
a
range of dates.

eg.
Records:
Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150

If the date to field is blank then today's date is to be used

At the moment I have the following:

startdate = "24/11/2003"
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * 120)

This works fine if the amount is always 120 and the start date is
always
24/11/2003.
How can I change it so that
bal = the addition of No. of weeks between each date in the table *
the
applicable amount?

The # of records will always be increasing - somehow I need to be able
to
calculate each record separately

Thanks

Thanks but I am not understanding . . .
the first line:
if isempty(cell) then
startdate = date
else
startdate = cell
end if

This is returning empty = true even though there is a date in cell A2
What am I doing wrong
Does the For always start at the first cell in the range ? in my example A2?

Thanks again for your help

"Tom Ogilvy" wrote in message
...
What your example showed was this:

Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150


Assuming Date From is in A1, that shows Data in A2:A4

I won't attempt to guess what your actual situation is. (receipt =
cells(6,3) ????)

I will amend the code to reflect what you originally said about any date
that is empty should be today's date (I only looked at column A)

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
If isempty(cell.offset(0,1)) then
EndDate = Date
Else
EndDate = cell.offset(0,2)
End if
if cell.offset(0,1) = "" and cell="" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, EndDate)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy



"Newbie" wrote in message
...
Thanks but the first if always returns true

I have the following:

Worksheets("Sheet1").Activate
For Each cell In Range("A1:A50")
If IsEmpty(cell) Then
startdate = Date
Else
startdate = cell
End If

My worksheet has data in row A1 and A2.

What am I doing wrong?
Thanks again

"Tom Ogilvy" wrote in message
...

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
if cell.offset(0,1) = "" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy


"Newbie" wrote in message
...
Hi

I want to be able to calculate a figure based on the no. of weeks
between
a
range of dates.

eg.
Records:
Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150

If the date to field is blank then today's date is to be used

At the moment I have the following:

startdate = "24/11/2003"
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * 120)

This works fine if the amount is always 120 and the start date is
always
24/11/2003.
How can I change it so that
bal = the addition of No. of weeks between each date in the table *
the
applicable amount?

The # of records will always be increasing - somehow I need to be
able
to
calculate each record separately

Thanks

for each cell in Range("A2:A50")

Cell will start in A2 of the activesheet.

--
Regards,
Tom Ogilvy

"Newbie" wrote in message
...
Thanks but I am not understanding . . .
the first line:
if isempty(cell) then
startdate = date
else
startdate = cell
end if

This is returning empty = true even though there is a date in cell A2
What am I doing wrong
Does the For always start at the first cell in the range ? in my example
A2?

Thanks again for your help

"Tom Ogilvy" wrote in message
...
What your example showed was this:

Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150


Assuming Date From is in A1, that shows Data in A2:A4

I won't attempt to guess what your actual situation is. (receipt =
cells(6,3) ????)

I will amend the code to reflect what you originally said about any date
that is empty should be today's date (I only looked at column A)

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
If isempty(cell.offset(0,1)) then
EndDate = Date
Else
EndDate = cell.offset(0,2)
End if
if cell.offset(0,1) = "" and cell="" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, EndDate)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy



"Newbie" wrote in message
...
Thanks but the first if always returns true

I have the following:

Worksheets("Sheet1").Activate
For Each cell In Range("A1:A50")
If IsEmpty(cell) Then
startdate = Date
Else
startdate = cell
End If

My worksheet has data in row A1 and A2.

What am I doing wrong?
Thanks again

"Tom Ogilvy" wrote in message
...

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
if cell.offset(0,1) = "" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy


"Newbie" wrote in message
...
Hi

I want to be able to calculate a figure based on the no. of weeks
between
a
range of dates.

eg.
Records:
Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150

If the date to field is blank then today's date is to be used

At the moment I have the following:

startdate = "24/11/2003"
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * 120)

This works fine if the amount is always 120 and the start date is
always
24/11/2003.
How can I change it so that
bal = the addition of No. of weeks between each date in the table
*
the
applicable amount?

The # of records will always be increasing - somehow I need to be
able
to
calculate each record separately

Thanks

Thanks.
To make this work I had to change the syntax slightly from
for each cell in Range("A2:A50")
to
for each cell in Range("A2","A50")
otherwise it always returned cell as being empty.

Thanks again for you help
"Tom Ogilvy" wrote in message
...
for each cell in Range("A2:A50")

Cell will start in A2 of the activesheet.

--
Regards,
Tom Ogilvy

"Newbie" wrote in message
...
Thanks but I am not understanding . . .
the first line:
if isempty(cell) then
startdate = date
else
startdate = cell
end if

This is returning empty = true even though there is a date in cell A2
What am I doing wrong
Does the For always start at the first cell in the range ? in my example
A2?

Thanks again for your help

"Tom Ogilvy" wrote in message
...
What your example showed was this:

Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150


Assuming Date From is in A1, that shows Data in A2:A4

I won't attempt to guess what your actual situation is. (receipt =
cells(6,3) ????)

I will amend the code to reflect what you originally said about any
date
that is empty should be today's date (I only looked at column A)

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
If isempty(cell.offset(0,1)) then
EndDate = Date
Else
EndDate = cell.offset(0,2)
End if
if cell.offset(0,1) = "" and cell="" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, EndDate)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy



"Newbie" wrote in message
...
Thanks but the first if always returns true

I have the following:

Worksheets("Sheet1").Activate
For Each cell In Range("A1:A50")
If IsEmpty(cell) Then
startdate = Date
Else
startdate = cell
End If

My worksheet has data in row A1 and A2.

What am I doing wrong?
Thanks again

"Tom Ogilvy" wrote in message
...

for each cell in Range("A2:A50")
if isempty(cell) then
startdate = date
else
startdate = cell
End if
if cell.offset(0,1) = "" then exit for
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
Amount = cell.offset(0,2).Value
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * Amount)
Next

--
Regards,
Tom Ogilvy


"Newbie" wrote in message
...
Hi

I want to be able to calculate a figure based on the no. of
weeks
between
a
range of dates.

eg.
Records:
Date From Date To Amount
3/11/03 17/11/03 120
18/11/03 7/12/03 130
8/12/03 - 150

If the date to field is blank then today's date is to be used

At the moment I have the following:

startdate = "24/11/2003"
Set fromsheet = ActiveSheet
Receipts = fromsheet.Cells(6, 3)
wks = DateDiff("ww", startdate, Now)
bal = Receipts - (wks * 120)

This works fine if the amount is always 120 and the start date
is
always
24/11/2003.
How can I change it so that
bal = the addition of No. of weeks between each date in the
table
*
the
applicable amount?

The # of records will always be increasing - somehow I need to
be
able
to
calculate each record separately

Thanks