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

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

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

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

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