1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Surely a simple Macro in Excel?

Discussion in 'Business Applications' started by andywiz, Aug 1, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. andywiz

    andywiz Thread Starter

    Joined:
    Jul 21, 2003
    Messages:
    6
    Hello :)

    I'm writing (rather than recording) macros for the first time.

    I need to get excel to select a cell on a specific row, within the same column as the current active cell.

    I have been trying...


    ActiveCell.Rows(17).Select


    ... but this just shifts the active cell down by 16 rows, rather than selecting the cell in row 17!

    I know this should be simple but I am finding Excel VBA help files atrociously difficult to follow.

    Hoping somebody can come to my rescue...

    Andy
     
  2. JudyB

    JudyB

    Joined:
    May 30, 2003
    Messages:
    98
    I'm not sure if this is what you want, but:

    If you want a "relative" reference (down or accross X number of rows) use this. The first offset number is rows up (-) or down, and the next number is rows left or right. So these examples will stay in the same column but move up or down X number rows.

    ActiveCell.Offset(-1, 0).Range("A1").Select (moves up one row)
    ActiveCell.Offset(4, 0).Range("A1").Select (moves down 4 rows)

    If it's a specific Cell Address that never changes, use this:

    Range("C2").Select

    Or if the cell address changes but you can name the range, use:
    Application.Goto Reference:="RangeName"

    There is also something like R1C1 (for row 1 column 1) I'm not sure of the exact use or syntax. But maybe someone else knows if this would work.

    Good Luck
     
  3. mrwendal

    mrwendal

    Joined:
    Jul 25, 2001
    Messages:
    377
    Hi,

    Here is another way you might want to try.

    If you know what row number you want to move to in the same column, you can try this (enter the row number in the "y = ...." line.


    Sub test()
    Dim x As Integer, y As Integer
    x = ActiveCell.Row
    x = -x
    y = 12
    ActiveCell.Offset(y + x, 0).Select
    End Sub

    This, for example, will move to the cell in row 12 in the same column as the active cell.
    I'm sure there are better ways, but this is just one!
     
  4. andywiz

    andywiz Thread Starter

    Joined:
    Jul 21, 2003
    Messages:
    6
    Thanks very much, both of you.

    In fact, a combination of both your suggestions has solved my problem, as well as helping me get to grips with simple VBA.

    Incidentally, I have also used:

    ActiveWindow.SmallScroll Down:=#

    ... to bring the new active cell to the top of the screen. This is easy as I know how far to scroll down each time, but do either of you know a better method of bringing the active cell to the top?

    Again, many thanks for your help, it's most appreciated.

    Andy
     
  5. mrwendal

    mrwendal

    Joined:
    Jul 25, 2001
    Messages:
    377
    You can always try this....

    Sub test()

    x = ActiveCell.Row
    y = ActiveCell.Column
    Worksheets("Sheet1").Activate
    ActiveWindow.ScrollRow = x
    ActiveWindow.ScrollColumn = y

    End Sub


    Hope this helps...
     
  6. andywiz

    andywiz Thread Starter

    Joined:
    Jul 21, 2003
    Messages:
    6
    mrwendal, thanks so much, this is genius.

    happy days!

    :D
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/151726

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice