Insert Cell On Condition And Move Right

    Hi all,


    I have made really huge downloads out of our SAP system (needed over 20 separate Excel files ;-). I need to analayze the data, but unfortunately the date is not 100% consistent as some cells are emtpy.


    Is it possible to have Excel look down a column, and have it insert a blank cell in another column on the same row where it found the empty cell? (and move the cells right a a result?)


    I have attached an example of what Excel should do as it is hard to explain (as you can tell ;-). Please can you help me on this? I am likely to spend the next days figuring out how I can best combine the data and analyze it!:rolleyes:


    Thanks in advance
    Koen

    Re: Insert Cell On Condition And Move Right


    Hi Koen,


    The following code should do it.


    Code
    Sub Replace()
    Dim lRow As Long
    lRow = Range("A65536").End(xlUp).Row
    For i = 2 To lRow
        If Range("I" & i).Value = "" Then
            Range(Range("G" & i), Range("I" & i)).Value = Range(Range("F" & i), Range("H" & i)).Value
            Range("F" & i).Value = 0
        End If
    Next i
End Sub


    Bill

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login