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.

Solved: Set cell selection based on a variable offset?

Discussion in 'Business Applications' started by Riverglen, Apr 12, 2012.

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

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    I know that this is probably a pretty trivial problem, but I can't seem to come up with the answer. I am trying to set the selection focus to a cell in a column, based on an offset variable. The following is a little macro that I created to experiment with. As written, it positions the selection focus on cell C6. But I can't find the way to set it to C6 + Offset. The only reason for the MsgBox statement is to prove that I passed the argument as expected.

    Sub MvTo(ByVal Offset As Long)

    Range("C6").Select
    MsgBox "Offset " & Offset

    End Sub

    I thought I could use RnCn cell references and write something like Range("R6[+Offset]C3").Select, but I get an error return, so I'm missing something somewhere. Run-time error '1004', Method 'Range' of object "_Global' failed.

    I am using Excel 2000.
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    the way you have it written you have to pass a value from another procedure, also the word "offset" is a reserved word so don't use it as a variable name ( I added a 1 to make it different).

    Code:
    Sub Test()
    
    dim Offset1 as Long
    
    Offset1 = 5
    
    
    MvTo (Offset1)
    
    End Sub
    
    
    Sub MvTo(ByVal Offset1 As Long)
    
    Range("C6").Offset(0, Offset1).Select
    MsgBox "Offset1 " & Offset1
    
    End Sub


    I don't know what you are trying to do or I would give you other ways of doing it... generally you don't have to select the cells, you just refer to them.
     
  3. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    Ziggy1,

    Thank you very much. You've solved my problem.

    In answer to your question, the overall problem I was trying to solve is that I have an x-y scatter plot in a chart on another sheet from where the source data is kept. I wanted to be able to click on a point in the scatter plot and be taken back to the source data sheet with one of the cells in the row the point came from selected. Click the point to find the line the data came from. Getting the selection in position on the proper row was the last piece of the puzzle.

    Needless to say, I'm not an experience enough VBA programmer to solve the larger problem, but someone pointed me to a very helpful web site that provided enough information to figure out how to get the point index by clicking on a point in the plot. The index-1 is the offset I needed to use to position the selection in the plot source data sheet.
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    good to hear you figured it out.

    fyi, I also use...

    Cells(R, C).Select

    R and C being variables

    so instead of using "offset" I manipulate the data going into the variables with math simply by...

    Cells(R, C+5).Select


    then again you can always add another variable that you use to update the offset


    Cells(R, C+O).Select
     
  5. Riverglen

    Riverglen Thread Starter

    Joined:
    Aug 28, 2006
    Messages:
    478
    First Name:
    Larry
    Hi,

    Thanks for the follow-up. Your last expression is close to what I had in mind. I used your original suggestion in my project, and it's working exactly the way it wanted.

    I have very little VBA experience, and am not at all familiar with all the functions/object properties and the proper syntax for using them. I have a big fat book on Excel programming. Think I'm going to spend some serious study time in actually reading it.
     
  6. 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/1049090

  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