calculate

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

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
 
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
 
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
 
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
 
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
 
Back
Top