View Single Post
  #2   Report Post  
Old April 22nd 09, 05:52 AM posted to microsoft.public.excel.programming
OssieMac OssieMac is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,510
Default Find data between dates

Hi Kirsty,

The logic of the code appears OK. However, you say "look for dates on one
sheet in cells A1 and A2 then match that to dates on another sheet in colum
D"

Your code does not look in column D on the second sheet for the match; it is
looking in column 1 (or A).

Perhaps it should be like this:-

For i = 1 To Sheets("Grades").UsedRange.Rows.Count
If Sheets("Grades").Cells(i, 3) = _
Sheets("Monthly reporting").Cells(1, 4) And _
Sheets("Grades").Cells(i, 1) <= _
Sheets("Monthly reporting").Cells(2, 4) Then

Sheets("Grades").Rows(i).Copy _
Sheets("Grade calc").Rows(j)
j = j + 1
End If
Next i


Did you also know that you can use "D" in lieu of the column number like the
following. I agree with someone on this forum that pointed this out to me
that it makes the code easier to read and debug if you know exactly what
column is being referred to.

j = 1
For i = 1 To Sheets("Grades").UsedRange.Rows.Count
If Sheets("Grades").Cells(i, 3) = _
Sheets("Monthly reporting").Cells(1, "D") And _
Sheets("Grades").Cells(i, 1) <= _
Sheets("Monthly reporting").Cells(2, "D") Then

Sheets("Grades").Rows(i).Copy _
Sheets("Grade calc").Rows(j)
j = j + 1
End If
Next i


--
Regards,

OssieMac