Selectivly copy data in Excel

I need to be able to copy data to a cell if it is empty, based on the closest cell with data in. For example, if Cell A1, A2 contains no data, cell A3 has the value 100, and cell A4 and A5 contain no data, and cell A6 has the value 200, then cell A2 and A4 should display 100, and cell A5 should display 200.

Does that make sense?

Many thanks,

Who is Participating?
Saurabh Singh TeotiaConnect With a Mentor Commented:
There you go, Use the following code it will do what you are looking for..
Enclosed is your workbook post i have run macro over it.

Sub fillvalues()
    Dim srow As Long, srow1 As Long
    Dim val As Variant
    srow = 3
    Do Until srow > Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        If Cells(srow, "G").Value = "" And Cells(srow, "F").Value <> "" Then
            srow1 = srow
            Do Until Cells(srow1, "g").Value <> ""
                srow1 = srow1 + 1
            val = Cells(srow + 1, "g").Value
            Cells(srow, "G").Value = val
        ElseIf Cells(srow, "g").Value = "" Then
            Cells(srow, "G").Value = val
        End If
        srow = srow + 1

End Sub

Open in new window

Does it need to be in the same sheet?  This could be accomplished quite easily if you use a separate spreadsheet or worksheet tab.  See spreadsheet attached for the "low tech" version.  The Base tab has the original numbers, the "Filled In" tab shows the formulas.  After the formulas are done, you could use copy paste values to put them back "filled in" into the original sheet.
You will have to specify a little more, but you can always show data in one cell depending on data in others.

One example is if you need A2 to be 100 if any data is put in A1, and otherwise empty:

A2: =IF(ISBLANK(A1);"";100)

Another example as above, but A2 now depends on both A1 and A3:

A2: =IF(AND(ISBLANK(A1);ISBLANK(A3));"";100)

But give us some more details on what you want?
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

BenwycliffeAuthor Commented:
Hi, Thanks for getting back. The data needs to be on the same sheet, as once the values are filled in, you can then filter them.
so the solution ImageryGrl came up with is what you want, just on one sheet? this is quiet easy, but will include some vba.

I will do it for you, but I am rather confused about what the point is :)

So same thing, but in one sheet only?

BenwycliffeAuthor Commented:

Perhaps I should explain a bit. Basically, the spreadsheet comes from an accounts package that pumps out it's data in a rather messy fashion. In the example, the column I am interested in is G. The codes going down G need to apply to the row above and below the row/s that have a code.

G3 and G5 need to display 810000. G18 and G24 would need to display 101001. So where there is no code, I want to apply the code that corresponds to the invoice.
RaahaugenConnect With a Mentor Commented:
Ok so a very simple solution without using vba will be to use the row next to G, as the code-row, since no formulas will then be overwritten whenever new data is pulled.

Starting from the cell I3: =IF(G2="";IF(G3="";IF(G4="";"";G4);G3);G2)

then simple drag the cell as long as you want down the I column.

another method is using vba as mentioned above.
BenwycliffeAuthor Commented:
Fantastic! That has done the job. I can now sort on the G column, and have all the codes in order.

Thanks for your help everyone.

BenwycliffeAuthor Commented:
Very quick and to the point. Huge happiness.
You're welcome.

That was my first answer on experts exchange :)
All Courses

From novice to tech pro — start learning today.