PDA

View Full Version : copy and paste active cell down a column



andytpl
06-27-2008, 01:09 AM
I like to create a macro that can automate the process of copying the active cell downward as long as the column D is not empty bearing in mind that there maybe empty rows in column D. It has to be intelligent enoug to skip empty rows.

Bob Phillips
06-27-2008, 01:28 AM
ActiveCell.Copy Activecell.Resize(Cells(Rows.Count,"D").End(xlUp).Row)

andytpl
06-27-2008, 01:39 AM
The macro is copying down as what I want to but I did not make it clear enough that if cell in column D is empty the corresponding cell in the active column should also be empty.

Bob Phillips
06-27-2008, 01:47 AM
Maybe this



With ActiveCell

.Offset(1, 0).Resize(Cells(Rows.Count, "D").End(xlUp).Row).FormulaR1C1 = _
"=IF(RC4="""","""",R" & .Row & "C)"
End With

andytpl
06-27-2008, 01:58 AM
I don't know what I am doing wrong, I could not get the result I want. While executing the code there is a circular reference error. Secondly only the cell below the active cell is copied but the content is not as the active cell. The data is the copied cell is "=IF($D2="","",D$1)". ???

Bob Phillips
06-27-2008, 02:00 AM
Which is the activecell when you run it?

andytpl
06-27-2008, 02:10 AM
My mistake. I have it the other way around. It is now working. thx

andytpl
06-27-2008, 02:14 AM
I spoke too fast. Although now the code is copying down the active cell but instead of copying the content of the active cell this is copied, "=IF($D2="","",D$1)"

Bob Phillips
06-27-2008, 02:26 AM
But isn't the result the value of the activecell?

Bob Phillips
06-27-2008, 02:27 AM
I also ask again, what is the activecell when you run this?

andytpl
06-27-2008, 02:34 AM
No, the active cell have a different value. The formula is Cell A1 (the active cell) is E1+F1

Bob Phillips
06-27-2008, 02:36 AM
E1+F1 is two cells, there is only one activecell.

It must be me, but you guys are really confusing me today.

Here is another suggesrion



Dim LastRow As Long
Dim rng As Range
With ActiveCell

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set rng = .Resize(LastRow).SpecialCells(xlCellTypeBlanks)
.Copy .Resize(LastRow)
rng.ClearContents
End With

andytpl
06-27-2008, 02:40 AM
Sorry, the active is A1 which has a formula of E1+F1.

Bob Phillips
06-27-2008, 02:47 AM
Modification of my last offering then



Dim LastRow As Long
Dim rng As Range
With ActiveCell

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set rng = Cells(.Row + 1, "D").Resize(LastRow).SpecialCells(xlCellTypeBlanks).Offset(0, .Column - 4)
.Copy .Resize(LastRow)
rng.ClearContents
End With

andytpl
06-28-2008, 12:46 AM
Thx. It now appears to be working the way I hope it would.

andytpl
06-28-2008, 12:46 AM
Thx. It now appears to be working the way I hope it would.

Bob Phillips
06-28-2008, 01:32 AM
Great, we finally got there :smile

andytpl
06-29-2008, 04:55 PM
Xld,

I wonder if you could help to improve on this macro. At the present moment this macro is very specific as to the reference "criteria" position, which is to the 4 position to the right of the active cell. What should the codes be if it to the right? Can a input box be created to ask for input of the reference cell? That will be wonderful if this can be done.

Thx in advance for any suggestionn and help.

Bob Phillips
06-30-2008, 12:13 AM
Try this



Dim LastRow As Long
Dim StartCell As Range
Dim rng As Range

Set StartCell = Application.InputBox("Select first cell of match range with the mouse", Type:=8)
If Not StartCell Is Nothing Then

With ActiveCell

LastRow = Cells(Rows.Count, StartCell.Column).End(xlUp).Row
.Offset(1, 0).Resize(LastRow - 1).ClearContents
Set rng = Cells(.Row + 1, StartCell.Column).Resize(LastRow).SpecialCells(xlCellTypeBlanks).Offset(0, .Column - StartCell.Column)
.Copy .Resize(LastRow)
rng.ClearContents
End With
End If

andytpl
06-30-2008, 06:20 AM
Xld,

The same problem as before. When pasting down the active cell it is suppose to skip over blank reference cell and continue pasting when the reference cell is not empty. Hope I make sense.

Bob Phillips
06-30-2008, 06:24 AM
No, not really.

andytpl
06-30-2008, 07:23 AM
What I meant and hope the macro is suppose to achieve is the macro allows the selection of the reference cell with an input box and then perform the same way as the previous macro and that is to copy from the active cell downward and skip a row or rows when reference cell or cells is empty and continue downward until the last filled reference cell. I hope I am clear.

andytpl
06-30-2008, 05:02 PM
Xld,

Discovered the source of the problem. This macro works with Office on a PC but not on a Mac. Now my problem is I have PC in the office while I have a Mac at home. Any idea what need to be changed?

andytpl
07-01-2008, 11:43 PM
xld,

After using the codes for a while I have made a little change to the codes. I discovered that if the active cell is starting from row 2, when copying down the active column it will always copy one more row down. This will not happen if it start in row 1. If I have omitted the statement Offset(1, 0).Resize(LastRow - 1).ClearContents this issue is overcome.

Public Sub copy_formula()

Dim LastRow As Long
Dim StartCell As Range
Dim Rng As Range

Set StartCell = Application.InputBox("Select first cell of match range with the mouse", Type:=8)
If Not StartCell Is Nothing Then

With ActiveCell

LastRow = Cells(Rows.Count, StartCell.Column).End(xlUp).Row
' Offset(1, 0).Resize(LastRow - 1).ClearContents
Set Rng = Cells(.Row + 1, StartCell.Column).Resize(LastRow).SpecialCells(xlCellTypeBlanks).Offset(0, .Column - StartCell.Column)
.Copy .Resize(LastRow)
Rng.ClearContents
End With
End If

End Sub

Bob Phillips
07-02-2008, 12:06 AM
I don't see how that fixes it, it is not the copy and I can see how it copies to far.

andytpl
07-02-2008, 12:49 AM
Xld,

You are right. The problem is with the starting row. Your codes works fine if starting row is row 1 but it will act funny if it start in other rows. What I mean is if the starting row is row 3 when copied down from the active cell 2 extra cells is copied even if the reference range do not have data. Any suggestion?