Help with VBA/Macros using Index/Match Function

Hello all,

I'm new to the forums and registered as a last-ditch effort to try and get this project completed. Help would be much appreciated.

I'm trying to create a macro button, but the problem is that I don't know proper VBA syntax.

I've imported a table of data into Excel:

I need the macro button to search column E for a range of numbers (These numbers cannot be sorted because each row is affiliated with the row directly beneath it or directly above it.) They come in sets of two, and I need to extract data from both of these rows to have the information required. The first row of the group has the Date in Column A and the bottom row of the group has the Time in Column A.

I've thought of the best way to do this, I just need to figure out how to tell excel to do it...

- I'm going to allow a range of numbers to be searched in column E (Ex: 400 - 450)
- For every number found in that range, it will look at column A of that row and check to see if the row is displaying the Date or the Time.
- If the row displays the Date in column A, then column E and column C of the row directly beneath it will extracted.
- If the row displays the Time in column A, then column E and column C of the row directly above it will be extracted.

So the button will return the info for the entire row of the values found.

-----
The other issue I have though is the format that the Date and Time is in. The time looks normal, but the date is shorthanded. For example, today's date would be written as: Mon 2/4

Is there any way that excel can still recognize this date format?
Because I'd also like to be able to search a row based on the numbers in column E AND column A (Date/Time)
-----
Also, the range of values varies, depending on the imported document. So I'd also need a function that can automatically figure out the range.

I've been reading on the INDEX, MATCH, and LOOKUP functions. But I'm still not sure about the best way to utilize these functions for my purpose.

Thanks a lot for reading, and for your feedback if possible.

Hi Bam

It's a bit difficult visualizing your issue without sample data.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.

One of us may be able to help.

Hi Bam

I haven't really looked at it yet but I think we can find this

Button 1 should search column E for the number range searched, and return the row information in addition to the entire row information for its partner row. The partner row is the row with the adjacent date or time.

But how is this demonstrated?

I'd also like to be able to search a row based on the numbers in column E AND column A (Date/Time)

And, what do you want to extract and to where do you wish it to be extracted (what the Output should be...the After).

Thread title changed.

I believe I need to figure out how to improve on this function:

Please Login or Register  to view this content.

- I need a function to give me a range, depending on the amount of data in Column E
- searchedValue will be a range of numbers searched by the user (User enters min and max number)
- Loop needs to be run in button macro that will return the values for every row containing a Column E value that falls within the searched range of numbers


Edit:
Dynamic Range (with dynamic rows and columns?) :

Please Login or Register  to view this content.

I'm getting an error though for the defined name that is given this value.

Edit 2:
Dynamic Range

Please Login or Register  to view this content.

No more error for range.

Hi Bam

I believe this Code finds the matches you describe here

the button will return the info for the entire row of the values found

You've not described

where do you wish it to be extracted

So, for the moment the Matches are indicated by an * (asterisk).

Let me know of issues.

Please Login or Register  to view this content.

Hi Bam

Moving the Matched Rows to another location added a bit to the Code. Let's see if this gives you the desired results on your Sample File then I'll try to do this

include comments to explain what you are doing

This Code will probably need to be modified for your Real Data because I'm using Column D of Sheet1 as a Marker Column so items don't get processed twice. In your real file, if Column D contains data you wish to be unchanged this won't work...but it can be made to work by modifying location of the Marker Column.

If the Column D issue is a problem I'll need to see the actual structure of your real file (sanitized for confidential information).

Play with it...let me know.

Please Login or Register  to view this content.

Hi Bam

Please don't post complete Quotes of a previous post...Quote only relevant parts of the Post.

I've commented the Code below...let me know of questions.

Please Login or Register  to view this content.