Use contents of one cell as part of formula elsewhere

[managementOptions]

In this particular task I have many rows of data and want to let the user select a row to bring to the top. Not move the row, just copy it into a blank row near the top of the spreadsheet.

First assume:

The data begins in row 10 and extends down to whatever length.
Row 3 is used for no other reason than to let the user see any line of data he likes without scrolling and searching through all of the rows.
The user uses Cell A1 to specify the row number he wants to see.

So if the user enters 10 into Cell A1, for example, row 10 would be copied to row three.

If the user then enters 15 into Cell A1, row 15 would be copied into row 3. Thus, the many rows of data would remain unchanged, but row 3 would always be reserved for a copy of whatever row number specified by the user.

My assumption is that I would do this with a macro attached to a button for the user. I assume that I would assign the value in cell A1 as the row number to copy and paste into row 10. But I really don't know how to assign the cell contents to the variable (is it a string or number, for example, when used as part of a cell address). I also don't know syntax for using the variable value in assigning the row to copy.

So there are a number of steps with which I need help:

I believe that this would be something like the below. I know that the below is not correct, but I believe it conveys what i want to do. That is, I believe I have the steps correct but the syntax not correct.

'Dim number as [string or integer?]
'myVariable= ActiveCell.("A1")
'ActiveCell..Rows("myVariable:myVariable").EntireRow.Select
'Selection.Copy
'ActiveCell.Rows("10:10").EntireRow.Select
'ActiveSheet.Paste

If anyone can help me correct the above to make it work I would be very grateful.

 

[SkipVought]

You have already been advised to post VBA code questions in forum707.

Please REPOST this question in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Malagar]

This can be solved with a simple INDEX command. No VBA required. If all you're hoping to accomplish is to bring data from a specific row number up to row 3 in the same column.

Try this:

=INDEX($A$10:$A$500,A1-9,1)

Copy this over changing your array to the corresponding column.

Let me know if this works for you


Malagar

 

[lionelhill]

Malagar is absolutely right, but don't discount the other relatively simple option of using a split window (to preserve the column-headings) and Excel's own "find and select" feature (the binoculas in Excel2007, home tab).