This is a very simple question, but its one I cant seem to figure out. All I need to do is fidn the code that will allow me to move up, down, etc from a cell without selecting specific cells to move to.
Right now If I want to move right (Im in Cell A1), I have to put in the follwing code: Range("A2").Select
ISNT THERE A WAY I CAN MOVE TO RIGHT, UP, DOWN ETC WITHOUT SPECIFYING A CERTAIN CELL?
Thanks
Re: Moving (Up, Down, etc.) to another cell, without clearly selecting a specific cell
Hi adilucb,
What are you trying to achieve here? If you don't specify where to go to, how can Excel know where you want to go. You can go to the last used cell in Column A for instance by:
but unless you can tell us what you are trying to achieve, it is very difficult to help.
Bill
Re: Moving (Up, Down, etc.) to another cell, without clearly selecting a specific cell
So I am really just trying to move down one cell, I have a macro written up which copies nad pastes data from one sheet to another, in order to have the macro paste the data next to and not over each the previous set of data I want to be able to move down a cell from my Activecell without specifying exactly which cell to go to.
If i tell it exactly which cell to go to everytime i run the macro it will paste over the previous information.
Re: Moving (Up, Down, etc.) to another cell, without clearly selecting a specific cell
What Bill posted will adjust every time you paste something. It goes to the bottom of the column, then goes back up to the last used cell. As you paste data into that column, the cell reference will change (but you don't have to change the code).
Re: Moving (Up, Down, etc.) to another cell, without clearly selecting a specific cell
Can we see your code?
Re: Moving (Up, Down, etc.) to another cell, without clearly selecting a specific cell
Sure I have added my code below. Much of the code was created using the macro recording option, so pelase excuse the amatuer level of the coding.
READ BEFORE CODE FOR CLARITY: The macro below copies data from sheet one which containt data organized by dates in Column A, the copied data is taken in 2 week intervals, so the week of the date selected and the following week are copied and taken to sheet2 for pasting. ONE OF THE PROBLEMS I AN HAVING: if I select a date which as an incomplete selected week the macro doesnt colec the data, I need to fix it so that if i select a date from the firt 5 dates for example, the macro will still collect data for as much of the week as is available.
SECOND PROBLEM:
Everytime I paste new data into the Sheet2, I have to create a new line over which I overlap newly pasted info so that all the data isnt just overwrite itself.
Code:
Sub Two_week_period_dataselection()
' Local Variables
Dim dteEnd As Date
Dim dteStart As Date
Dim intRowCount As Integer
' Verify selection contains a valid date
If Not IsDate(Selection.Range("A1")) Then Exit Sub
' Set date selection range
dteStart = DateValue(DateAdd("d", -Weekday(Selection.Range("A1")) + 1, Selection.Range("A1")))
dteEnd = DateAdd("d", 13, dteStart)
' Find size of data and select
intRowCount = 0
Do While Selection.Range("A1").Offset(intRowCount, 0) >= dteStart
intRowCount = intRowCount - 1
Loop
Selection.Range("A1").Offset(intRowCount + 1, 0).Select
intRowCount = 1
Do While Selection.Range("A1").Offset(intRowCount + 1, 0) <= dteEnd
intRowCount = intRowCount + 1
Loop
Range(Selection, Selection.Range("A1").Offset(intRowCount, 0)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet2").Select
ActiveCell.Activate
ActiveSheet.Paste
'Creating extra highlighted line, indicating end of 2 week interval
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.Insert Shift:=xlDown
'highlighting of line
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'Now I am clearingt he contents of the extra copied line, so that I can insert the next set of data which will overlap over the deleted row
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Selection.End(xlToLeft).Select
Sheets("Sheet1").Select
End Sub
Re: Moving (Up, Down, etc.) to another cell, without clearly selecting a specific cell
Range("A65536").End(xldown).Select
This script will take me to the last cell, thats NOT what I want to do. I want to be able to step down to the next row without telling the macro to go to A2 for example.
Because I am looping the script if i tell it to go down it will select a specific cell and then always go to that cell, that means my data will be pasted over itself repeatedly. PLEASE HELP, IF I AM STILL UNCLEAR LET ME KNOW.
Re: Moving (Up, Down, etc.) to another cell, without clearly selecting a specific cell
:loo:
This is a very simple question, but its one I cant seem to figure out. All I need to do is fidn the code that will allow me to move up, down, etc from a cell without selecting specific cells to move to.
Right now If I want to move right (Im in Cell A1), I have to put in the follwing code: Range("A2").Select
ISNT THERE A WAY I CAN MOVE TO RIGHT, UP, DOWN ETC WITHOUT SPECIFYING A CERTAIN CELL?
Thanks :ole:
Don’t have an account yet? Register yourself now and be a part of our community!
