Fill cell with value above it (with validation).
On Wednesday, September 17, 2014 11:44:12 AM UTC-5, BMPInc. NOT wrote:
Hello all,
I�m hoping to get some assistance with the following:
I have column �A� and �B�.
Column �B� has some cells with 4 dashes (----). I would like to replace
the dashes with the value from the cell directly above it. There might
be one cell with dashes or many contiguous cells with dashes in column
�B�.
The other thing I�m trying to do is make sure the value in the cell to
the left of the dashes, and the value directly above that cell, are the
same. If they are not, the dashes should not be replaced. Like this:
Befo
COLA | COLB
Part1 | PO1
Part1 | ----
Part1 | ----
Part2 | ----
Part3 | PO2
Part3 | ----
After:
COLA | COLB
Part1 | PO1
Part1 | PO1
Part1 | PO1
Part2 | ----
Part3 | PO2
Part3 | PO2
I found the code below on extendoffice.com. It works great if the cells
are blank (instead of filled with dashes). This code also requires that
I have a cell in column �B� selected. I�m trying to make it so I don�t
have to worry about what cell is selected. And, I need to add the
validation step.
Any help is appreciated.
Thanks!
Sub AddPOToBlanks()
'fill blank cells in column with value above
Dim Area As Range, LastRow As Long
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
| | SearchDirection:=xlPrevious, _
| | LookIn:=xlFormulas).Row
For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
| | SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub
Forgot about this but could probably be modified to use a
FINDNEXT macro. Send me the file with my first macro, etc.
--
BMPInc.
|