Solved

Return a row

Posted on 2011-09-28
11
217 Views
Last Modified: 2012-05-12
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?

0
Comment
Question by:usatrfe
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 5

Expert Comment

by:GirardAndrew
ID: 36715706
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
 
LVL 5

Expert Comment

by:DerZauberer
ID: 36715765
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36715767
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 33

Expert Comment

by:jppinto
ID: 36715768
Take a look at this article:

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

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 36715779
Here's an example on how to apply the MATCH function...

jppinto
MATCH.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36715789
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36715936
Your code should be:
rowref = Application.Match(targetdate, Range("A1", "A100"), 0)

Open in new window

0
 

Author Comment

by:usatrfe
ID: 36716020
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 36717508
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
 

Author Closing Comment

by:usatrfe
ID: 36813043
super!
Thanks for that.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36813322
No problems, glad to be of assistance!
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question