How to capture row addresses?

Hi

I have a dataset whereby I need to look through it to find a pre-defined search value. Once found, I have code to copy the entire row and paste it onto another sheet. For this, I am fine.

What I would like to do is add a functionality that will record on the other sheet, the row addresses from where they originated.

For example:
Dataset
Row Value
1 100
2 100
3 101
4 101
5 101
6 102
7 102

Search value = 101

I want to know they are found in rows 3 to 5

I was trying to modify this code (see below) but without success....

http://siddharthrout.wordpress.com/2...-in-excel-vba/

Any suggestions?

Hi there, yes I know it's in there, I just can't seem to figure out how to pull it out...

warning: it's quite long!

Please Login or Register  to view this content.

I am already counting the rows, and the data stays together when it is copied... but I wanted to know where on the ws.Source tab the data was coming from before it's being copied to the Archive tab. I was going to populate the row addresses (i.e. Rows(3:15)) in a column on the Archive tab, I was thinking in the first row of data for each audit #.

Any advise/direction would be greatly appreciated!

Hi xladept,

Yes I do.. but not sure how to pass it onto another sheet. I've posted the code under the last comment. Thanks!

hmmm my reply didn't show up!

Thank you so much for the help! I moved that line of code Mike7952 to the ws.Dest section as that's where the rows were I wanted to know about. However, is there a way to place the infomation onto another tab instead of in the Immediate window?

I want to know which row was the starting one and which was the ending one... like the Min and Max of the selection...

I wonder... If I can caputre that first row it selects, which is actually the last row, and subtract from the BUcount I've already done, I'll get the first row... and past that into the Archive tab then.... wheels are turning.....

Hi Mike7952

I'm not quite sure how to intergrate that idea into the code...
I was thinking... since your first idea pulls out ALL the rows... how can we manilpulate it to just pull out the FIRST row it finds... when I tested it, the row it pulled out first was actually the last row (it was 1666 in my real dataset) becauase it was looping through End(xlUp). How do we get it to STOP as soon as it finds a match, even if there are more afterwards?

Would the FIND function work? Can we make it go end up?

I did it!!

I used the FIND function!

Thank you for all your help!

Please Login or Register  to view this content.