VBA to Populate columm based on criteria

[Aspiring_Excel_Master]

Hi

I have tried to create the VBA code below to populate values in column B but cannot get it to work.

Essentially my spreadsheet looks like this with numbers and text in column A. Whereever there is a numeric value in column A, I would like to copy it and place it one cell above in column B.

So in the first instance, it would take the value from A3 (0.1151) and place next to Product
in B2:

A1
A2 product
A3 0.1151
A4
A5
A6 Open
A7 -0.0141
A8


This is the code I have so far:

Sub Rearrange()


last = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To last
If IsNumeric(Cells(i, "A")) = "False" Then


'Cells(i, "A").Copy
'Cells(i - 1, "B").Select
'Selection.PasteSpecial Paste:=xlPasteValues
Resume Next


Else:


r = Cells(i, "A").Row
rpre = r - 1
MsgBox rpre
Cells(rpre, 2).Value = Cells(i, "A").Value
End If


Next i


End Sub

Thanks

AEM

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub MoveNumbers()
   Dim rng As Range
   For Each rng In Range("A:A").SpecialCells(xlConstants, xlNumbers).Areas
      rng.Offset(-1, 1).Value = rng.Value
   Next rng
End Sub

 

[Aspiring_Excel_Master]

Hi & welcome to MrExcel
How about

Sub MoveNumbers()
   Dim rng As Range
   For Each rng In Range("A:A").SpecialCells(xlConstants, xlNumbers).Areas
      rng.Offset(-1, 1).Value = rng.Value
   Next rng
End Sub

Thanks Fluff, this works and seems much simplier than my approach!

 

[Fluff]

Glad to help & thanks for the feedback