Return a row

I have an Excel 2007 spreadsheet with, amongst other columns, a column of dates.
In VBA I need to find a particular date and then return its row reference.

I have googles this and the function Match is suggested.
But Match throws a compile error 'sub or function not defined'.

Here is just a bit of what I have tried:-

Sub get_row()
    Dim rowref As string
    Dim targetdate As Date
     
    rowref = Match(targetdate, Range(A1, A100), 0)
       
End Sub
I would have thought the rowref would have the row & column reference of the match.


Any ideas?

usatrfeAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
This looks like a standard filter.

Select the Data area and from the Data Menu/Tab select Auto Filter.

This will add dropdowns at the top of each column.

Use the drop down on the date column to Select Custom:

Criteria1 - Greater than or Equal to - 08/09/2011
AND
Criteria2 - Less than or Equal to - 14/09/2011

Click OK.

Once filtered if you now copy the data, only the visible cells will be copied.

If you have more elaborate criteria, particularly if you have more than two per column, there is alos Advanced Filter. The Criteria for this gets specified in a separate range with headers the same as the column headers for the data range. This can be filtered in place or can be copied to another location. When doing it manually, rather than via VBA, select the destination sheet for the copied data before starting the Advanced Filter routine.

Thanks
Rob H
0
 
GirardAndrewCommented:
Hi,

I would guess the error is due to the value of the targetdate variable.

Can you provide a sample worksheet? How do you get the value of targetdate - another cell or input?

Thanks,
Girard Andrew
0
 
DerZaubererCommented:
I don't know of match function, but you can use "find" to get a range object ...

targetdate=#2011-09-28#

result = Cells.Find(targetdate)

result.Address ' gives the cell name f.e. (including row)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Rob HensonFinance AnalystCommented:
I have seen a number of comments on here where the Match function does not work as expected within VBA, even using WorksheetFunction.Match(...

I think its because there are complications on how it evaluates the range within the calculation.

How about using a MATCH formula within the workbook and then referring to that cell within your Sub.

=MATCH(TODAY(),A1:A100,0)   Would give the row of the current date within the range A1:A100

You could give this cell a Named Range eg TODAYROW then your Sub would be:

Sub get_row()
    Dim rowref As Integer
    rowref = Range("TODAYROW").Value
End Sub

Open in new window


Thanks
Rob H
0
 
jppintoCommented:
Take a look at this article:

http://excel-user.blogspot.com/search/label/MATCH

jppinto
0
 
jppintoCommented:
Here's an example on how to apply the MATCH function...

jppinto
MATCH.xlsx
0
 
Rob HensonFinance AnalystCommented:
BTW MATCH will return the relative row within the range.

For example if you had the days of September in range A1:A30, today would be row 28 and

=MATCH(TODAY(),A1:A30,0)

would return 28

If the dates were in range A6:A35 and the formula was

=MATCH(TODAY(),A6:A35,0)

The result would still be 28 eventhough todays date is now on row 33 but it is the 28th row in the range.

Thanks
Rob H
0
 
Rory ArchibaldCommented:
Your code should be:
rowref = Application.Match(targetdate, Range("A1", "A100"), 0)

Open in new window

0
 
usatrfeAuthor Commented:
OK so it looks like MATCH with VBA is not the correct way to go.

I have attached a (basic) spreadsheet that I plan to use.
Here is what I need to do:-
- delete all rows where there is no value in column A. The 'items' info will be deleted too - that is OK.
- Find all rows between two dates (column B). Lets say between 8/9/2011 & 14/9/2011.
    the first date of 8/9/2011 to the last date of 14/9/2011 - a total of 4 rows.
- copy those rows only to a new sheet as a report.

Incidentally I'm using dd/mm/yyyy format for the date.

I would have thought that this could be achieved without VBA as it seems to be a pretty standard kind of report.
But I am stuck to find out how!.




RFE2.xlsx
0
 
usatrfeAuthor Commented:
super!
Thanks for that.
0
 
Rob HensonFinance AnalystCommented:
No problems, glad to be of assistance!
0
All Courses

From novice to tech pro — start learning today.