Create a Macro to copy cells.

[Hector Codoceo]

Hi,
I need to create a macro to put the content of a cell "D2"
in others inmediatelly down "D3, D4" but only if they are
empty cells.
My problem is, that I don't know when it's gona be a empty
cell.

example.

A B C D
--------------------------------------
1 Peterson Micke 25 Computer
2 Smith Ron 22 Radio
3 Welch Carl 27
4 Codoceo Hector 20
5 Stone Linda 21
6 Blair John 28 Computer
7 Thomas Aquino 30

so, like the example, I need to fill the cells D3, D4, D5
widh the information in cell D1, and do the same widh put
the information from D6 into D7.

I receive 300 excel files widh this problem every day, and
only fill the cell empty take a lot of time.

I Need elp, Please.
Thanks
Hector

[Mike Tomasura]

try this

Private Sub CommandButton1_Click()
For x = 3 To 40
If Range("D" & x) = "" Then
Range("D" & x) = Range("D2")
End If
Next x
End Sub

Hector Codoceo <hector...@hotmail.com> wrote in message
news:0bda01c31ae1$91ed5600$a301...@phx.gbl...

[Hector Codoceo]

thanks a lot
it's work, but.
I need to do the same with the information in cell D6,
that mean put the information from D6 into D7
like this.

A B C D
--------------------------------------
1 Peterson Micke 25 Computer
2 Smith Ron 22 Radio

3 Welch Carl 27 Radio <it's from D3
4 Codoceo Hector 20 Radio <it's from D3
5 Stone Linda 21 Radio <it's from D3

6 Blair John 28 Computer

7 Thomas Aquino 30 Computer < it's from D6
8 Reyes Maria 21 Computer < it's from D6
so when it's found a cell in D, with information, stop
paste and start with the next cell without information,
but now paste the information from the first cell up (with
information).
sorry, for the inconvenience
and thanks a lot
Hector

>.
>

[Mike Tomasura]

try this

Private Sub CommandButton1_Click()

y = 3 'the starting row

For x = 3 To 40

If Range("D" & x) = "" Then

Range("D" & x) = Range("D" & y)
Else
y = x
End If

Next x

End Sub

Hector Codoceo <hector...@hotmail.com> wrote in message

news:0c7b01c31afb$05a94a40$a501...@phx.gbl...

[Myrna Larson]

I think you have a typo in your message.

>I need to fill the cells D3, D4, D5 with the information in cell D1, and ...

>the information from D6 into D7

You mean to fill D3:D5 with the value from D2, not D1, right?

Mike's sub does what you describe in the 1st sentence: it puts the value from D2 into every
blank cell in the column.

I am interpreting the problem differently. I assume that a blank cell is to be filled with data
from the previous non-blank cell, i.e. D2 goes to D3:D5, D6 goes to D7, etc.

This sub does that, and should be faster to execute than processing the cells one at a time.
Speed may be an issue with 300 files per day.

Sub FillBlanks()
Dim ColumnD As Range
Dim Blanks As Range

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

On Error Resume Next

With ActiveSheet
'don't include row 1, since there's no row above and formula
'will fail: D1 =D65536, but other cells end up with =#REF!
Set ColumnD = Intersect(.UsedRange, .Range("D2:D65536"))
End With
If ColumnD Is Nothing Then Exit Sub

Set Blanks = ColumnD.SpecialCells(xlCellTypeBlanks)
If Blanks Is Nothing Then Exit Sub

With Blanks
.FormulaR1C1 = "=R[-1]C"
.Calculate
End With

With ColumnD
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

With Application
.CutCopyMode = False
.Calculation = xlCalculationAutomatic
.ScreenUpdating = False
End With

End Sub