EXCEL- Wrong Copy Next Cell

[sitzalke]

Hi. I have a problem which are my cell wrongly copy data. It supposed to copy really next cell, but it copy the data below next cell. This is the example,

when column "L" is blank or empty, then it will copy next cell in column M.

column L	column M
	1
	2
3	3

<tbody>
</tbody>

	4
	5
	6
3	7

<tbody>
</tbody>

it supposed to be like this,

column L	column M
1	1
2	2
3	3

<tbody>
</tbody>

4	4
5	5
6	6
3	7

<tbody>
</tbody>

but my output is like this,
it copy cell from below cell in 5.

column L	column M
2	1
3	2
3	3

<tbody>
</tbody>

5	4
6	5
7	6
3	7

<tbody>
</tbody>

this is my code,

Sub BlankNcopy()
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 in advance

 

[Michael M]

MAybe this then

Sub BlankNcopy()
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).Value  ' this should be the same line NOT the line below
Next c
Application.ScreenUpdating = True


End Sub

 

[sitzalke]

MAybe this then

Sub BlankNcopy()
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).Value  ' this should be the same line NOT the line below
Next c
Application.ScreenUpdating = True


End Sub

yeah, it works. But, the thing is, it only copy first row when I run the macro. but, if I run twice, only then it will copy all row.

for example,
column L are all blank or empty.

column L	column M
	1
	2
	3
	4
	5

<tbody>
</tbody>

then I run the macro, the output is like this,

column L	column M
1	1
	2
	3
	4
	5

<tbody>
</tbody>

then I run twice, it be like this,

column L	column M
1	1
2	2
3	3
4	4
5	5

<tbody>
</tbody>

my question is, how to make it run only one time to copy all?

Thanks for the reply

 

[DanteAmor]

Try this

Sub BlankNcopy()
    On Error GoTo Noblanks
    With Range("L2", Range("L" & Rows.Count).End(xlUp))
        .SpecialCells(xlCellTypeBlanks) = "=IF(RC[1]="""","""",RC[1])"
        .Value = .Value
    End With
    Exit Sub
Noblanks:
    MsgBox "No blank cells in col L"
End Sub