Solved

Return a row

Posted on 2011-09-28
11
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

732 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