Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Fill cell with value above it (with validation).
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- BMPInc. should do it Sub showifdashs() Dim I As Long Dim mf As Range Application.ScreenUpdating = 0 For I = 2 To Cells(Rows.Count, "a").End(xlUp).Row Set mf = Columns("b").Find(What:="----", LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not mf Is Nothing And _ Cells(I - 1, 1) = Cells(I, 1) Then Cells(I, 2) = Cells(I - 1, 2) End If Next I Application.ScreenUpdating = 1 End Sub |
#3
|
|||
|
|||
dguillett,
It works perfectly! Thanks for your time, help and quick response! Dan |
#4
|
|||
|
|||
Hi Don,
The macro does exactly what I was hoping it would do. I thank you again for your hard work. I was wondering if you might have any suggestions for getting this to run more efficiently in a large spreadsheet. I currently have about 34,000 lines and almost 4,000 of them have the dashes (----) and it takes 15 minutes to run. Any suggestions are appreciated. Thanks again for your help, Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove fill in text from a cell from an unpopulated fill-in cell | Excel Worksheet Functions | |||
Blank cell w/data validation & automatic fill in? | Excel Programming | |||
Validation List to fill adjacent cell with IF? | Excel Discussion (Misc queries) | |||
validation (list) & auto fill | Excel Worksheet Functions | |||
Can Validation against a list do auto-fill in a cell? | Excel Discussion (Misc queries) |