Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 22nd 09, 04:35 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 37
Default Find data between dates

I have a macro that is designed to look for dates on one sheet in cells A1
and A2 then match that to dates on another sheet in colum D before putting
all the information into a third sheet. I have this macro

j = 1
For i = 1 To Sheets("Grades").UsedRange.Rows.Count
If (Sheets("Grades").Cells(i, 3) = Sheets("Monthly
reporting").Cells(1, 1) And Sheets("Grades").Cells(i, 1) <= Sheets("Monthly
reporting").Cells(2, 1)) Then
Sheets("Grades").Rows(i).Copy Sheets("Grade calc").Rows(j)
j = j + 1
End If
Next i

and it is not working. Any suggestions

  #2   Report Post  
Old April 22nd 09, 05:52 AM posted to microsoft.public.excel.programming
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumifs with data and dates - I can not figure out the dates gary davis Excel Worksheet Functions 3 January 19th 10 05:01 AM
compare 2 tables of dates to find the preceding dates Babi Excel Worksheet Functions 3 October 28th 08 06:52 AM
For Next does not find dates? CG Rosén Excel Programming 3 March 24th 08 12:27 PM
How do I find the earliest dates in a range of dates? JJ Excel Worksheet Functions 3 May 16th 06 09:36 AM
Charting data against dates where dates are not at fixed intervals PK Charts and Charting in Excel 4 June 16th 05 05:08 AM


All times are GMT +1. The time now is 09:22 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017