Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fill with Above (Excel 97 SR2)

    Hi, I have a macro that I got off the internet, likely from this lounge, a few years ago. I made the mistake of giving it to a co-worker and now that co-worker wants the macro modified <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    What she's doing is taking data from a pivot table, copying that data to a new worksheet, using the fill with above macro to copy data down in the blank cells, and then doing a vlookup on that copied column.

    The trouble is the vlookup only works on the first (original) row. The other ones return N/A. If you copy the top cell down the vlookup works, but pasting formats doesn't work. Both cells say they're formatted as general. She doesn't want to have to do any copying or anything manually, she wants it all done automatically through the macro or formulas. She has tried to multiply the column by 1, but she said that caused some other kind of problem, though I didn't see it to know what problem she's talking about.

    Can anyone tweak the macro to copy all of the information down instead of just the values?

    Thanks,

    Brett

    Sub FillWithAbove()
    '
    ' Fill With Above Macro
    ' Macro recorded 10/29/99


    Dim WithWhat As Variant
    iRows = Selection.Rows.Count
    iColumns = Selection.Columns.Count
    For iC = 1 To iColumns
    WithWhat = Selection.Item(1, iC).Value
    For iR = 1 To iRows
    If Selection.Item(iR, iC).Value = "" Then
    Selection.Item(iR, iC).Value = WithWhat
    Else
    WithWhat = Selection.Item(iR, iC).Value
    End If
    Next iR
    Next iC
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fill with Above (Excel 97 SR2)

    This macro will copy and paste the cell above to an empty cell. You or your co-worker should test carefully whether it works as intended.

    Sub FillWithAbove()
    Dim iRows As Long, iR As Long
    Dim iColumns As Long, iC As Long
    iRows = Selection.Rows.Count
    iColumns = Selection.Columns.Count
    For iC = 1 To iColumns
    For iR = 2 To iRows
    If Selection.Item(iR, iC).Value = "" Then
    ' Copy cell above and paste into empty cell
    Selection.Item(iR - 1, iC).Copy Selection.Item(iR, iC)
    End If
    Next iR
    Next iC
    End Sub

    Note that this version starts at row 2 of the selection, to prevent errors with empty cells in row 1 of a worksheet.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill with Above (Excel 97 SR2)

    Hans,

    Thanks! That seems to work. As you advised, we'll keep an eye on the results to make sure it's working entirely as intended, but our initial test showed success!

    Thanks again!

    Brett

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fill with Above (Excel 97 SR2)

    Another fast way

    Sub FillWithAbove()
    '
    Selection.CurrentRegion.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    Range("A1").Select
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •