PDA

View Full Version : [SOLVED:] Vba find and replace with loop



SEBTATA
05-25-2016, 08:58 AM
Hi,
I am new in VBA.
I have one spreadsheet with about 3000 items.
Its is purchasing report.
The order numbers appear only once and they may have 10 or more items.
I need excel to put in the column A the order number for each item as now it appears only for item 1 in column B.
So I tried to change all blank cells in A to "check above".
This way I was trying something like:


If cell.Value = "CHECK ABOVE" Then
ActiveCell.Offset(-1, 0).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
Loop

Please help me to put this in the proper way.
Thank you very much.

mdmackillop
05-25-2016, 11:02 AM
Give this a try

Sub FillOrder()
Dim r As Range, cel As Range
Set r = Columns("B:B").SpecialCells(xlCellTypeConstants)
Set r = r.Offset(, -1).SpecialCells(xlCellTypeBlanks)
For Each cel In r
cel.Value = cel.Offset(-1).Value
Next
End Sub

SEBTATA
05-26-2016, 12:55 AM
It worked just as needed!
Much appreciated.