Loop to test if cell not empty then move value to next column?

[hazmat]

Looking for code help to loop through A1:A10, and if A is Not Empty, the value of A will be moved over 1 column, to B.
Existing Data In B cannot be disturbed. The Data in Column A is being generated by a Formula.

I have the following code for the 1st 3 cells, but just don't have the knowledge to create a loop for the whole range of cells (Which will ultimately be AE8:AE157 values moved to AF8:AF157, respectively).
Been searching for hours with no luck. Thanks for any help.

Private Sub DataTransfer()

If Not IsEmpty(Range("a1").Value) Then
Range("B1").Value = Range("A1").Value
Range("A1").Clear
End If

If Not IsEmpty(Range("a2").Value) Then
Range("B2").Value = Range("A2").Value
Range("A2").Clear
End If

If Not IsEmpty(Range("a3").Value) Then
Range("B3").Value = Range("A3").Value
Range("A3").Clear
End If

End Sub

From:

To This:

 

[footoo]

A1, A3, and A8 are not empty. Why are the values in these cells not moved to column B ?
Also what is meant by "Existing Data In B cannot be disturbed" ?

 

[jolivanes]

Try so.

Sub Maybe()
Dim lr As Long, c As Range
lr = Range("A:B").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    For Each c In Range("B1:B" & lr)
        If Len(c) = 0 And c.Offset(, -1).Value <> "" Then
            With c
                .NumberFormat = "@"
                .Value = Format(c.Offset(, -1).Value, "d-mmm")
            End With
        End If
    Next c
End Sub

 

[hazmat]

A1, A3, and A8 are not empty. Why are the values in these cells not moved to column B ?
Also what is meant by "Existing Data In B cannot be disturbed" ?

A1, A3 and A8 do get moved to B
The first image is what I start with, and the 2nd image is what i need to happen.
The snippet of code i shown works fine, but I would have to copy it 147 more times for the whole range i ultimately want to move, if the Not IsEmpty is met.
I just don't know how to write the loop, to make the the code more efficient.

The "Existing Data In B cannot be disturbed" means that the data In B cannot be moved, overwritten, deleted etc., even temporarily.
The way my Workbook works is that if there is data in a particular Cell in Column A, the adjacent Cell in B will always be empty

 

[footoo]

Does column B contain formulas?

 

[hazmat]

Does column B contain formulas?

No Formulas in B

 

[footoo]

Try this :

Sub v()
With [B:B].SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1])"
    [B:B].Value = [B:B].Value
    .Offset(0, -1).ClearContents
End With
End Sub

 

[jolivanes]

What is the result when running the macro from Post #3?

To delete the copied value from Column A, change the "With.....End With" to so

            With c
                .NumberFormat = "@"
                .Value = Format(c.Offset(, -1).Value, "d-mmm")
                .Offset(, -1).ClearContents    '<---- Insert this line
            End With

 

[hazmat]

What is the result when running the macro from Post #3?

To delete the copied value from Column A, change the "With.....End With" to so

            With c
                .NumberFormat = "@"
                .Value = Format(c.Offset(, -1).Value, "d-mmm")
                .Offset(, -1).ClearContents    '<---- Insert this line
            End With

With the added ClearContents line, it is almost there. See image below....Something to do with the Format?

 

[jolivanes]

The values copied across are formatted as text.

Insert one more line.

            With c
                .NumberFormat = "@"
                .HorizontalAlignment = xlRight    '<---- Insert this line
                .Value = Format(c.Offset(, -1).Value, "d-mmm")
                .Offset(, -1).ClearContents
            End With