With the above, once the selection changes on the second line of code the 'a' active row location number will be different as the current selected cell has changed
a=Activecell.Row Activecell.Row range("table1[location]").Select selection.End(xlup).select Offset(a, 0).select
Is there a way to store the row number in the first 'ActiveCell.Row' line of code for reference in the 'Offset(a,0).Select
Many thanks
Last edited by hb316; 09-11-2013 at 06:33 AM.
It's not very clear what you're trying to do, but the linewill have already stored the row number of the original active cell. That's assuming that this is the start of your macro.a = Activecell.Row
It may help to post more of your macro code up to help with context.
As it is, your code is using a row number to create an offset for the column being selected. I only point that out as your code differs from the line in your question where you have the variable 'a' offseting the row not the column.
Syntax is Offset([row],[column])
Please consider the * button if my post helped you
Hi, hb316,
maybe try it like this
Dim lngRow As Long lngRow = ActiveCell.Row Application.Goto reference:=Range("table1[location]").Cells(lngRow - 1, 1)won´t take you to the right (Columns) but down (Rows). So you should not look for the row but rather the column and add 15 to that.Offset(ActiveCell.Row, 0).Select
Ciao,Dim lngRow As Long Dim lngCol As Long With ActiveCell lngRow = .Row lngCol = .Column End With Application.Goto reference:=Range("table1[location]").Cells(lngRow - 1, lngCol).Offset(0, 15)
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
yeah sorry, have changed the offset Syntax now
ActiveCell.Row Range("table1[location]]").Select Selection.End(xlUp).Select Offset(ActiveCell.Row, 0).Select
what Im trying to do is move horizontal to another location on a spreadsheet with a command button click instead of having to scroll
with the code above i'm looking to move across to a column however would like to remain on the same active cell row when moving
so if im on cell a100 on the first column and would like to move to the 'location' column which is 15 columns to the right, I would click on the command button and it will move to the column and keep me on the same row.
youre right bodi808 Activecell.row does store
the code below worked perfect.
Dim aCellrow As Integer aCellrow = ActiveCell.Row Range("table1[location]").Select Selection.End(xlUp).Select ActiveCell.Offset(aCellrow, 0).Select
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks