Solved

Return a row

Posted on 2011-09-28
11
214 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 31

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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 31

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 31

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 31

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now