Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Return a row

Posted on 2011-09-28
11
Medium Priority
?
225 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

636 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