Tech Support Guy banner
Status
Not open for further replies.

Solved: Set cell selection based on a variable offset?

1K views 4 replies 2 participants last post by  Riverglen 
#1 ·
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 ·
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 ·
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 ·
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 ·
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.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top