Copy/Paste Excel Macro

Hello,

What I'm trying to do is automate a process using an excel macro. What
I need the macro to do is to look for a blank cell in a range of data
in Column A, and whenever there is a blank space within that range,
paste some specific above information into this row. (I'm formatting
an excel file after taking it from another program). I would greatly
appreciate any help!

Thank you!
0
Justin
1/7/2010 8:43:16 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
698 Views

Similar Articles

[PageSpeed] 42

You could record a macro to filter on blank in coloum A then paste a formula 
+cell above- this would then fill in your blanks
-- 
Thanks for your help


"Justin" wrote:

> Hello,
> 
> What I'm trying to do is automate a process using an excel macro. What
> I need the macro to do is to look for a blank cell in a range of data
> in Column A, and whenever there is a blank space within that range,
> paste some specific above information into this row. (I'm formatting
> an excel file after taking it from another program). I would greatly
> appreciate any help!
> 
> Thank you!
> .
> 
0
Utf
1/7/2010 9:49:02 PM
Sub Fill_Blanks()
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

    Set wks = ActiveSheet
    With wks
       Col = .Range("A1").column 
'or
'Col = ActiveCell.Column
   
        Set Rng = .UsedRange        'try to reset the lastcell
        LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
        Set Rng = Nothing
        On Error Resume Next
        Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
                .Cells.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0

        If Rng Is Nothing Then
            MsgBox "No blanks found"
            Exit Sub
        Else
             Rng.NumberFormat = "General"
            Rng.FormulaR1C1 = "=R[-1]C"
        End If

    'replace formulas with values
        With .Cells(1, Col).EntireColumn
            .Value = .Value
        End With

    End With

End Sub


Gord Dibben  MS Excel MVP

On Thu, 7 Jan 2010 12:43:16 -0800 (PST), Justin <jmc06004@gmail.com> wrote:

>Hello,
>
>What I'm trying to do is automate a process using an excel macro. What
>I need the macro to do is to look for a blank cell in a range of data
>in Column A, and whenever there is a blank space within that range,
>paste some specific above information into this row. (I'm formatting
>an excel file after taking it from another program). I would greatly
>appreciate any help!
>
>Thank you!

0
Gord
1/7/2010 11:03:03 PM
Your description of the problem is too vague for suggesting code that will 
do the job.  But here is a sample that would do some of what you describe:

Sub fillBlanks()
   Dim lr As Long, rng As Range
   Dim sh As Worksheet, c As Range
   Set sh = ActiveSheet
   lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
   Set rng = sh.Range("A1:A" & lr)
   For Each c In rng
     If c.Row <> 1 Then
       If c.Value = "" And c.Offset(-1, 0) > "" Then
         c.Value = c.Offset(-1, 0).Value
       End If
     End If
   Next
End Sub



"Justin" <jmc06004@gmail.com> wrote in message 
news:d7857b10-c6df-4c38-9278-b2e8ee74fcb9@e27g2000yqd.googlegroups.com...
> Hello,
>
> What I'm trying to do is automate a process using an excel macro. What
> I need the macro to do is to look for a blank cell in a range of data
> in Column A, and whenever there is a blank space within that range,
> paste some specific above information into this row. (I'm formatting
> an excel file after taking it from another program). I would greatly
> appreciate any help!
>
> Thank you! 


0
JLGWhiz
1/7/2010 11:04:39 PM
THANK YOU SO MUCH!! THIS GREATLY HELPED ME AT WORK! With a little
modification, I was able to get it to copy all the cells I need in
just one macro!

THANKS AGAIN!
-JUSTIN
0
Justin
1/8/2010 3:41:53 PM
Reply:

Similar Artilces: