Offset

[DavidM]

Hi all

I've been trying to get this code to paste to the right of any data in
Column T until it comes to a blank cell, then stops.
I've changed the offset values yet cannot get it right. Help appreciated

Header in row 1
Data in Row 2

Application.Goto Reference:="R1C21"
Range("U1").End(xlDown).Select
Do
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Selection.ClearContents
End Sub

Thank in Advance

Dave

 

[Guest]

ActiveCell.offset(1,0).Range("A1").Select
to simply - that seems to work but sure is a strange statement.
ActiveCell.Offset(1,0).Select

Go through your code line by line and write down what it is doing and be
specific with sheet names and cell references and I think you'll see where
you may be having troubles.
Or set a break point or Stop command right before the
Range("U1").End(xlDown).Select statement and then use [F8] to single step
through the code to see exactly what it is doing.

I'm a bit confused as to what you are trying to copy from or put where. As
I understand it, you want to get some value from somewhere (via
Selection.Copy - but what cell/cells is that information in?) and then paste
it next to any entry in column T until it comes to a blank cell and then
quits. A blank cell where? In T? or over in column U, as I'm thinking
perhaps you're overwriting existing data in that column?

According to your code you're starting off in column U:
Range("U1").End(xlDown).Select
then inside of the Do loop you're copying the value there and moving down 1
row:
ActiveCell.Offset(1,0).Select
then you paste the data
but the
Loop Until IsEmpty...
statement is testing for what is in column V via .Offset(0, 1
If you want to look at column T, since you're in U, use
Loop Until IsEmpty(ActiveCell.Offset(0,-1))
The -1 will make it look 1 column to the LEFT instead of 1 column to the
right.

 

[Guest]

Activecell.offset(0,-1) is column T

Application.Goto Reference:="R1C21"
Range("U1").End(xlDown).Select
Do
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
Selection.ClearContents
End Sub

I think I would use something like

Sub AddData()
Dim rng As Range, rng1 As Range
Set rng = Cells(Rows.Count, "T").End(xlUp).Offset(0, 1)
Set rng1 = Cells(Rows.Count, "U").End(xlUp)(2)
If rng1.Row >= rng.Row Or rng1.Row = 1 Then Exit Sub
Range(rng, rng1).Value = rng1.Offset(-1, 0)
End Sub

If I understand what you are doing.

 

[DavidM]

Hi JLatham and Tom

Thanks for you replies

I have a Header U1 some Text in U2, below U2 Blank cells.

Header T1, Some Data T2 to T10.

I would like to Goto U1 Then Find and select the last cell in column U that
contains data, Copy that cell, then paste to U3 to U10, to the right of
Column T. Or if was T22, paste to U22


Tom your code works fine, By its self I need to do this to other columns,
and I don't think I could use your code 3 or 4 times in a Marco.


This code works fine pasting to the left of Data, I'm Trying to paste to the
right.

Application.Goto Reference:="R1C21"
Range("U1").End(xlDown).Select

Do
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste


Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Selection.ClearContents
End Sub


Hope I've been clear

Dave

 

[Guest]

did you try

Loop Until IsEmpty(ActiveCell.Offset(0, -1))

as we both suggested.

 

[DavidM]

Hi Tom

I tried the code
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

and it pasted the Content of AH2,which is right, to AI2 to AV2.
I'm wanting it to paste to AH3 to AH10 which is to the right of any data
cells in column T until it finds a blank cell then stops.

Thanks for your reply

Dave

 

[Guest]

Sub AAA()
Range("AH1").End(xlDown).Select
Selection.Copy ActiveCell.Offset(1, 1)
ActiveCell.Offset(1, 0).Select
Do
ActiveCell.Offset(0, 1).Copy _
ActiveCell.Offset(1, 1)
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(Cells(ActiveCell.Row, "T"))
ActiveCell.Offset(0, 1).ClearContents
End Sub

 

[Guest]

David,
Tom and I have been working under the assumption that everything was pretty
much happening in columns T and U, we didn't know about AH.

Lets try this instead?
Sub CopyDataTest()
Dim LastColumn As Long
Dim myRowOffset As Long
'find last used column
LastColumn = Range("A2").Offset(0, Columns.Count - 1).End(xlToLeft).Column
'adjust LastColumn to use as an offset from column A
LastColumn = LastColumn - 1
'next get into your test column, T at 1st data item
Range("T2").Select
myRowOffset = ActiveCell.Row - 1
Do Until IsEmpty(ActiveCell.Offset(myRowOffset, 0))
Range("A1").Offset(myRowOffset + 1, LastColumn) = _
Range("A1").Offset(myRowOffset, LastColumn)
myRowOffset = myRowOffset + 1
Loop
End Sub

No cut and paste and no actual movement from cell to cell. This should do
what I think you want, and do it pretty fast.

Here's what it's doing: first it looks for the last used column in row 2 so
as to find the last column with data in it (since you could have headers row
1, but no data under them). It looks from the right edge back left, toward
column A, so that it does not get fooled by any empty cells on a row between
A and the last one with data in it. It takes the column number it finds and
subtracts one from it so that we can use that as an offset from column A to
the column with the data in it.

We move to the first data cell of your 'test' column, T2 and just stay
there! but we find out what row that is (although we know, maybe it won't
always be on row 2, so this allows you to use a different starting row). We
adjust that value to again, use as an offset from this location to test for
an empty cell.
Then we just loop and increment the myRowOffset pointer so that we can test
the proper cell in column T for emptiness, and also use it to 'copy' the data
that's out in the far right column on down the sheet.

To test, I put entries into cells T2:T6 and put an entry into AH2 only. Ran
the code and ended up with the value from AH2 repeated down in all cells to
AH6.

I know it looks a little odd, but once you figure out what I've done to get
the initial values for LastColumn and myRowOffset it becomes clearer.

Remember that .Offset uses the values as an offset from the current active
cell unless you tell it differently. To get the values to be copied/pasted,
I told it specifically to use an offset from cell A1, but to test for the
empty cell, it uses an offset based on the ActiveCell, which we 'forced' to
be T2 and we don't change that during the process.

Hope this helps some with the problem.