Assigning cell contents to a variable

[rmccafferty]

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.

 

[njimack]

Something like this? It will fire whenever the contents of A1 are changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
If Target.Address = "$A$1" Then
    r = Target.Row

    Cells(r, 1).EntireRow.Copy Destination:=Cells(3, 1)

End If

End Sub

 

[Smitty]

Wouldn't a formula do it:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Row #</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">101</td><td style="text-align: right;;">102</td><td style="text-align: right;;">103</td><td style="text-align: right;;">104</td><td style="text-align: right;;">105</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">111</td><td style="text-align: right;;">112</td><td style="text-align: right;;">113</td><td style="text-align: right;;">114</td><td style="text-align: right;;">115</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">121</td><td style="text-align: right;;">122</td><td style="text-align: right;;">123</td><td style="text-align: right;;">124</td><td style="text-align: right;;">125</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">131</td><td style="text-align: right;;">132</td><td style="text-align: right;;">133</td><td style="text-align: right;;">134</td><td style="text-align: right;;">135</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">141</td><td style="text-align: right;;">142</td><td style="text-align: right;;">143</td><td style="text-align: right;;">144</td><td style="text-align: right;;">145</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">151</td><td style="text-align: right;;">152</td><td style="text-align: right;;">153</td><td style="text-align: right;;">154</td><td style="text-align: right;;">155</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">161</td><td style="text-align: right;;">162</td><td style="text-align: right;;">163</td><td style="text-align: right;;">164</td><td style="text-align: right;;">165</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A3</th><td style="text-align:left">=INDEX(<font color="Blue">A$10:A$34,$B$1-9,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=INDEX(<font color="Blue">B$10:B$34,$B$1-9,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=INDEX(<font color="Blue">C$10:C$34,$B$1-9,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D3</th><td style="text-align:left">=INDEX(<font color="Blue">D$10:D$34,$B$1-9,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E3</th><td style="text-align:left">=INDEX(<font color="Blue">E$10:E$34,$B$1-9,0</font>)</td></tr></tbody></table></td></tr></table><br />

HTH,

 

[rmccafferty]

I must confess that I have never used the Index function. My understanding was that it finds the cell for a value.

Although I use a reasonable amount of VBA in Excel and Access, I am certainly not expert in it. And I really do not understand how to implement the answers that I have received. There are things about both which I do not understand.

In the answer with the code wherein the variable r equals the row number, I did not know that the CELL designation mattered.

And I have never used the INDEX function and still don't understand how it is being used in the example. All I think I know about INDEX is that it finds the location (row and column) for a value.

First, here is a simplified example of what I have:


-- removed inline image ---


If the user puts the number 8 in cell B1, then I would like to have row 8 copied to row 4. If they put 10 in B1, row 10 would be copies to row 4.

So, using the example put forth by njimack, what might the code be if I want to copy as per the above example, Copy row 8 to Row 4 because the number 10 is in cell A3. The copy TO row of 4 will not change. Ever. The only variable involved is the value in A1, which is the row number to copy FROM.

So in pseudocode, I think it would something like

dim r as Long
r=the number in A3
Copy row r
move to row 4
paste row

If anyone can make that into real code, I would be very grateful.




<table style="width: 249px; height: 216px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height:15.0pt" height="20"><td style="height:15.0pt;width:91pt" width="121" height="20">
</td><td class="xl63" style="width:48pt" width="64" align="right">
</td><td style="width:48pt" width="64">
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td class="xl64" style="height:15.0pt" height="20">
</td><td class="xl64">
</td><td class="xl64">
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr></tbody></table>