Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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. "DavidM" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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. -- Regards, Tom Ogilvy "DavidM" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "DavidM" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
did you try
Loop Until IsEmpty(ActiveCell.Offset(0, -1)) as we both suggested. -- Regards, Tom Ogilvy "DavidM" wrote: 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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "DavidM" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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 "Tom Ogilvy" wrote in message ... did you try Loop Until IsEmpty(ActiveCell.Offset(0, -1)) as we both suggested. -- Regards, Tom Ogilvy "DavidM" wrote: 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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "DavidM" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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 -- Regards, Tom Ogilvy "DavidM" wrote: 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 "Tom Ogilvy" wrote in message ... did you try Loop Until IsEmpty(ActiveCell.Offset(0, -1)) as we both suggested. -- Regards, Tom Ogilvy "DavidM" wrote: 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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "DavidM" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset
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. "DavidM" wrote: 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 "Tom Ogilvy" wrote in message ... did you try Loop Until IsEmpty(ActiveCell.Offset(0, -1)) as we both suggested. -- Regards, Tom Ogilvy "DavidM" wrote: 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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "DavidM" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
select offset (variable ,1) to offset(variable ,variable) | Excel Programming | |||
OFFSET PLEASE HELP! | Excel Discussion (Misc queries) | |||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming |