select row where value in column matches text field

[Rerock]

Hi all,
I have a table that's say, 1000 rows, and occasionally one row of the table needs to be copied and pasted so that their are two entries.

I intend to have a cell where the user can enter a number, and the macro would copy the row of the table that has the matching number in Column A.


Let's say the user entered the number "387" in to the cell, then clicked the button to run the macro.

The macro would look in range A4:A1003 for the value "387", select that row, copy, and insert it one row down.


Any ideas?

[stnkynts]

Please Login or Register  to view this content.

[Rerock]

Thanks Stnky!

That's a really good start for what I'm needing to accomplish...

Couple of questions:

How can I change "r.Insert", so that the copied line is inserted below "r.EntireRow" instead of above?
After the copied row is inserted, I need to clear any contents that are in columns Q:U for that inserted row.
Lastly, Column A is a formula to generate the numbers... Cell A5 formula is: =A4+1... After inserting the copied row, I will need to select the cell in column A, and copy the formula down one row to update the formula that was copied/inserted.

I think that is everything that needs to happen, can you please help me expand it al little bit?

[stnkynts]

How can I change "r.Insert", so that the copied line is inserted below "r.EntireRow" instead of above?

r.Offset(1).Insert

After the copied row is inserted, I need to clear any contents that are in columns Q:U for that inserted row.

Range("Q" & r.Row + 1, "U" & r.Row + 1).ClearContents

Lastly, Column A is a formula to generate the numbers... Cell A5 formula is: =A4+1... After inserting the copied row, I will need to select the cell in column A, and copy the formula down one row to update the formula that was copied/inserted.

r.Resize(2, 1).FillDown

[Rerock]

r.Offset(1).Insert



Range("Q" & r.Row + 1, "U" & r.Row + 1).ClearContents



r.Resize(2, 1).FillDown

Thanks!!!!

Though "r.Resize(2, 1).FillDown" didn't work quite right, but thanks to the code you provided, I came up with:

Please Login or Register  to view this content.

and that did the trick!

Thank you so much for your help!