Copy next Cell when cell is blank

[sitzalke]

Hi. I am beginner to excel vba. I would like to ask simple question that I've been stuck for few days.
So far, I have two columns, for example column L and M.
I have to loop all column L and find the blank cell. If there are a blank cell, then, it will copy next value in column M.
Roughly, the column is like this,
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column L[/TD]
[TD]Column M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

so supposedly second row of column L will be 4. The flow will be until end of the lastrow.
So far, my code is like this,

  Dim c As Range    Dim searchrange As Range
    Dim i As Long


    Set searchrange = Range("L2", Cells(Rows.Count, 1).End(xlUp))


    For i = searchrange.Cells.Count To 1 Step -1
        Set c = searchrange.Cells(i)
        If c.Value = "" Then
        'c.Value.Copy Range("M2")
       c.Value.Copy Destination:=Range("M")
        End If
    Next i

Thanks in advance

 

[Michael M]

looks a little like an assignment. Is that ALL of the question ?
And what happens when M gets to zero, does it go into negative numbers ?

 

[JoeMo]

Assuming no blanks in col M over the range of data in col L:

Sub Sitzalke()
Dim R As Range, Rblank As Range, c As Range
Set R = Range("L2", Cells(Rows.Count, "L").End(xlUp))
On Error Resume Next
Set Rblank = R.SpecialCells(xlCellTypeBlanks)
If Rblank Is Nothing Then
    MsgBox "No blank cells in col L)"
    Exit Sub
End If
Application.ScreenUpdating = False
For Each c In R.SpecialCells(xlCellTypeBlanks)
    c.Value = c.Offset(1, 1).Value
Next c
Application.ScreenUpdating = True
End Sub

 

[Michael M]

OR

Sub MM1()
Dim cell As Range
For Each cell In Range("L2", Cells(Rows.Count, 12).End(xlUp))
    If cell.Value = "" Then cell.Offset(, 1).Value = cell.Offset(-1, 1).Value - 1
    Next cell
End Sub

 

[sitzalke]

Assuming no blanks in col M over the range of data in col L:

Sub Sitzalke()
Dim R As Range, Rblank As Range, c As Range
Set R = Range("L2", Cells(Rows.Count, "L").End(xlUp))
On Error Resume Next
Set Rblank = R.SpecialCells(xlCellTypeBlanks)
If Rblank Is Nothing Then
    MsgBox "No blank cells in col L)"
    Exit Sub
End If
Application.ScreenUpdating = False
For Each c In R.SpecialCells(xlCellTypeBlanks)
    c.Value = c.Offset(1, 1).Value
Next c
Application.ScreenUpdating = True
End Sub

Thanks this is working. A lot. May God Bless you for this kindness. Thanks again

 

[JoeMo]

Thanks this is working. A lot. May God Bless you for this kindness. Thanks again

You are welcome - thanks for the reply.