| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
R1C1 referencing
Hi
The following in A1 notation needs to be converted to R1C1 notation, such that I can add a 1 to the row part of A29. In other words the formula in A2 needs to be copied down 1 extra cell each time a macro runs. Range("A2").Select Selection.Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Select Selection.Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False Would this be the way to do it (x is arrived at from counting the non-blank cells in a column) Range(Cells(2, 1)).Select Selection.Copy Range(Cells(3, 1), Cells(x + 1, 1)).Select ActiveSheet.Paste Application.CutCopyMode = False Range(Cells(2, 3)).Select Selection.Copy Range(Cells(3, 2), Cells(x + 1, 2)).Select ActiveSheet.Paste Application.CutCopyMode = False Sandy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
R1C1 referencing
I did a bit of quick cleanup. I'm sure there's more
Range("A2").Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False This determines the last row in column C (for example) lrow= cells(rows.count,3).end(xlup).row You could change your range to Range("C3:C" & lrow + 1).Select "Sandy" wrote: Hi The following in A1 notation needs to be converted to R1C1 notation, such that I can add a 1 to the row part of A29. In other words the formula in A2 needs to be copied down 1 extra cell each time a macro runs. Range("A2").Select Selection.Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Select Selection.Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False Would this be the way to do it (x is arrived at from counting the non-blank cells in a column) Range(Cells(2, 1)).Select Selection.Copy Range(Cells(3, 1), Cells(x + 1, 1)).Select ActiveSheet.Paste Application.CutCopyMode = False Range(Cells(2, 3)).Select Selection.Copy Range(Cells(3, 2), Cells(x + 1, 2)).Select ActiveSheet.Paste Application.CutCopyMode = False Sandy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
R1C1 referencing
Excellent thank you
Sandy "Barb Reinhardt" wrote in message ... I did a bit of quick cleanup. I'm sure there's more Range("A2").Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False This determines the last row in column C (for example) lrow= cells(rows.count,3).end(xlup).row You could change your range to Range("C3:C" & lrow + 1).Select "Sandy" wrote: Hi The following in A1 notation needs to be converted to R1C1 notation, such that I can add a 1 to the row part of A29. In other words the formula in A2 needs to be copied down 1 extra cell each time a macro runs. Range("A2").Select Selection.Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Select Selection.Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False Would this be the way to do it (x is arrived at from counting the non-blank cells in a column) Range(Cells(2, 1)).Select Selection.Copy Range(Cells(3, 1), Cells(x + 1, 1)).Select ActiveSheet.Paste Application.CutCopyMode = False Range(Cells(2, 3)).Select Selection.Copy Range(Cells(3, 2), Cells(x + 1, 2)).Select ActiveSheet.Paste Application.CutCopyMode = False Sandy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
R1C1 referencing
Without all the selects and pastes.
lrow = Cells(Rows.Count, 3).End(xlUp).Row ActiveSheet.Range("A2").Copy _ Destination:=ActiveSheet.Range("C3:C" & lrow + 1) But I don't understand why A3:A29 was selected for first paste then C3:C29 for second paste. Gord Dibben MS Excel MVP On Sat, 28 Apr 2007 04:32:00 -0700, Barb Reinhardt wrote: I did a bit of quick cleanup. I'm sure there's more Range("A2").Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False This determines the last row in column C (for example) lrow= cells(rows.count,3).end(xlup).row You could change your range to Range("C3:C" & lrow + 1).Select "Sandy" wrote: Hi The following in A1 notation needs to be converted to R1C1 notation, such that I can add a 1 to the row part of A29. In other words the formula in A2 needs to be copied down 1 extra cell each time a macro runs. Range("A2").Select Selection.Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Select Selection.Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False Would this be the way to do it (x is arrived at from counting the non-blank cells in a column) Range(Cells(2, 1)).Select Selection.Copy Range(Cells(3, 1), Cells(x + 1, 1)).Select ActiveSheet.Paste Application.CutCopyMode = False Range(Cells(2, 3)).Select Selection.Copy Range(Cells(3, 2), Cells(x + 1, 2)).Select ActiveSheet.Paste Application.CutCopyMode = False Sandy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
R1C1 referencing
Works great thank you
"Barb Reinhardt" wrote in message ... I did a bit of quick cleanup. I'm sure there's more Range("A2").Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False This determines the last row in column C (for example) lrow= cells(rows.count,3).end(xlup).row You could change your range to Range("C3:C" & lrow + 1).Select "Sandy" wrote: Hi The following in A1 notation needs to be converted to R1C1 notation, such that I can add a 1 to the row part of A29. In other words the formula in A2 needs to be copied down 1 extra cell each time a macro runs. Range("A2").Select Selection.Copy Range("A3:A29").Select ActiveSheet.Paste Application.CutCopyMode = False Range("A3").Select Selection.Copy Range("C3:C29").Select ActiveSheet.Paste Application.CutCopyMode = False Would this be the way to do it (x is arrived at from counting the non-blank cells in a column) Range(Cells(2, 1)).Select Selection.Copy Range(Cells(3, 1), Cells(x + 1, 1)).Select ActiveSheet.Paste Application.CutCopyMode = False Range(Cells(2, 3)).Select Selection.Copy Range(Cells(3, 2), Cells(x + 1, 2)).Select ActiveSheet.Paste Application.CutCopyMode = False Sandy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| R1C1 | Excel Discussion (Misc queries) | |||
| A:1 now R1C1 | Excel Worksheet Functions | |||
| Using a cell value for row in R1C1 | Excel Worksheet Functions | |||
| R1C1 versus A1 | New Users to Excel | |||
| R1C1 reference | Excel Discussion (Misc queries) | |||
Hybrid Mode
