[rmccafferty]

In a macro in Excel I want to refer to a cell where the user entered a number. I want that number to be the row number for the macro to move to. (I will then select the entire row and copy it to row 1, which I know how to do).

How can I tell the macro which row to go to, using the number that has, in this case, been entered into cell A3? That is, if the user entered the number 11 in cell A3, how to I tell the macro to to to A11?

I assume I need to use a variable. If so, I know how to declare the variable, but not the syntax for assigning a value to the variable in Excel or now to use that variable within a reference to a cell location.

Thanks,
Robert
[email address]

[Advertisements]

Dim r As Integer


r = Range("A3").Value
Cells(r, 1).Select

[Jonesey]

Dim r As Integer


r = Range("A3").Value
Cells(r, 1).Select

[rmccafferty]

Thank you. Someone else answered my question in another forum and didn't have it right. I didn't have to test it to know it wasn't quite right. The difference in using VBA in Excel rather than Access sometimes drives me crazy.

Again, thank you.

[Jonesey]

You're welcome.

You dont have to use the Dim statement to declare the variable, but it's good programming technique to do so.

[rmccafferty]

I am trying to use the following to copy the contents of a row to to row 1. And the entry in cell A3 tells what row to select for this copying routine.

Dim r As Integer


r = Range("A3").Value
Cells(r, 1).EntireRow.Select
'
Selection.Copy
Cells(a, 1).Select
ActiveSheet.Paste


End Sub

When I run this, is seems to choke on the line that sets the value for the variable r

In practice, when I type 11 into cell A3, I want the macro to look and cell A3 and know that i want to copy the contents of row 11 to row 1. On that line I get error message 1004: Method:_'Default' of Object Range' failed.

So I must be selecting the variable value wrong. Anyone know the correct way to do this?

Robert

[Jonesey]

Right, that's a different question!!!!

I think this should work

Assuming cell A3 contains the number 11

Dim r as Integer

r = Range("A3").Value
Cells(r, 1).EntireRow.Select
Selection.Copy
Cells(1, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

That's not the most elegant way of writing this macro, but it should work

If not, let me know.




Edit: your bit of code didn't work because you used cells(a,1) as a reference

the Cells property uses row & column NUMBERS, not letters

Edited by Jonesey, 31 July 2008 - 08:21 AM.