Solved I wish to return row/column references from Excel data

December 5, 2014 at 06:04:37
Specs: Windows 7, core i3 / 4Mb RAM
How do I...
Have an Excel table with dates in A (col 1) and financial data in column G (col 7). I wish to enter 2 dates into a macro and from these find cell references to enable me to eg print partial table, display charts etc.
There may be a simple way to do this but I have written a dummy macro below to achieve this. Can use this to return rows / column data for the dates but not do anything with this. For simplicity I have just tried to highlight the range chosen but excel does not recognise my attempt (last line before end of macro). NB I have tried numerous variations on the theme. Msg boxes etc are used for diagnostics and would be removed from code.

Sub dateTest()

' Select a range depending on Date

Dim D1 As Date
Dim D2 As Date

Dim R1 As Single
Dim R2 As Single

Dim C1 As Single
Dim C2 As Single

'find D1 (first date)
D1 = InputBox("Enter Start date")
Do Until ActiveCell.Value >= D1
ActiveCell.Offset(1, 0).Select

'if specified date does not eexist go up one row
If ActiveCell.Value > D1 Then
ActiveCell.Offset(-1, 0).Select
'do nothing
End If
R1 = ActiveCell.Row
C1 = ActiveCell.Column
MsgBox "The First Row is " & R1
MsgBox "The FirstColumn is " & C1
'find D2 (second date)
D2 = InputBox("Enter End date")
Do Until ActiveCell.Value >= D2
ActiveCell.Offset(1, 0).Select

'if specified date does not eexist go up one row
If ActiveCell.Value > D2 Then
ActiveCell.Offset(-1, 0).Select
'do nothing
End If
ActiveCell.Offset(0, 6).Select
R2 = ActiveCell.Row
C2 = ActiveCell.Column
MsgBox "The Second Row is " & R2
MsgBox "The Second Column is " & C2

'Select the discovered range

End Sub

Any suggestions?

See More: I wish to return row/column references from Excel data

December 5, 2014 at 08:49:38
✔ Best Answer
First a posting tip:

Please click on my signature line at the bottom of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read.

In addition, you might want to check out this How-To as a means to help you troubleshoot your code:

As for one of your questions, this line fails because the syntax is wrong:


Since C1, R1, etc. are integers, you should use the Cells method to refer to the Range:

Range(Cells(R1, C1), Cells(R2, C2)).Select

Finally, I would use the .Find method to directly find the dates instead of looping through Column A. I would also use the Application.InputBox method instead of just InputBox since that will allow you click on the date cell instead of manually entering it. A manual entry will still work assuming the user doesn't make a typing mistake, but clicking the desired cell is much safer. The code below should work regardless of which date you enter/select first.

This code is pretty basic, with no error checking, etc. but it seems to get the job done.

Sub DateRangeSelection()
Dim D1 As Date, D2 As Date
Dim startDate As Range, endDate As Range
'Get Start date from user
      D1 = Application.InputBox("Enter Start date")
'Get End Date from user
      D2 = Application.InputBox("Enter End date")
'Find dates in Column A
        With Columns(1)
         Set startDate = .Find(D1)
         Set endDate = .Find(D2)
        End With
'Select the range
      Range(startDate.Address & ":" & _
      Range(endDate.Address).Offset(0, 6).Address).Select
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

December 5, 2014 at 17:57:27
1. Will follow the VBA posting requirements in future - was not aware of these (see below).
2. Ref the "Range-Cells method - will do. I came close to this in one attempt but could not recall syntax.
3. Agree no error checking as yet That given was basic attempt to get workable code and will refine in final version. NB when I did a lot of this I would load in checks as I perceived necessary then hand over to my (then) teenage son. He would take great delight in finding potential problems and so proving that his stupid father could be bested. He would get his satisfaction, I would get the feedback I wanted.
Think this covers all. Should be able to continue from here and complete the project. Many thanks for help and comments.
Is there a guide to site conventions, help files (similar to those you quote above) and even archives so that I can research without bothering others in future? If so would appreciate details.

Report •

December 6, 2014 at 05:55:45
re: help files

There is a link to the How-Tos section of this website in the menu bar at the top of each page. An How-To is an article written by a member if this group typically after the same question has come up multiple times. Instead of answering the question over and over again, a member will put together a neatly packaged answer, get it approved by the site administrator and have it posted as a How-To.

re: archives

The search bar at the top of this page will allow you to search the site for previous questions and answers. In order to keep the archives as organized as possible and make searches more efficient, there are 2 main conventions to follow:

1 - Use a subject line that is relevant to the question in your post, just like you did. If members use subject lines like "I need help with a formula" (which happens occasionally) it makes searching for past articles difficult because you can't tell what the question is really about.

2 - Try not to ask more than one unrelated question in a thread. For example, you asked about code to return row-column references. Once a solution is offered, you shouldn't say "Thanks! Now can you tell me why my SUMPRODUCT formula doesn't work?" If a member is searching for information related to SUMPRODUCT, he probably wouldn't open a thread with a subject line related to row-column references. The flip side of that is to not open more than one thread for the same question. The goal is to keep all related information together so it is easy to follow.

There is sometimes a fine line between a followup question and an "unrelated" question, so it's often a judgement call. Usually, other members will let you know (nicely) when the new question should be in its own thread, with its own subject line.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question