Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Macro VBA - go to a row number specific in a cell


  • Please log in to reply

#1
rmccafferty

rmccafferty

    Member

  • Member
  • PipPip
  • 16 posts
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]
  • 0

Advertisements


#2
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Dim r As Integer


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

#3
rmccafferty

rmccafferty

    Member

  • Topic Starter
  • Member
  • PipPip
  • 16 posts
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.
  • 0

#4
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
You're welcome.

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

#5
rmccafferty

rmccafferty

    Member

  • Topic Starter
  • Member
  • PipPip
  • 16 posts
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
  • 0

#6
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
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.

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP