DUMP DATA INTO NEW COLUMN FROM SEARCH OF OTHER COLUMN

Resolved · High Priority · Version 2010

Fri 24 Oct 2014, 12:11 Delegate Adrian said...

Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course

DUMP DATA INTO NEW COLUMN FROM SEARCH OF OTHER COLUMN

Hi

I have a column (lets say A:A) where cells either contains the letter D or is blank. For all the cells that are blank, I want it to dump the corresponding cell entry in another column ie if A5 was blank, then I want in column C to show data in Cell B5. However, I want column C to be come a list ie the Data from column B is in cells C1, C2, C3 etc. Example below

Column A Column B Column C
D ABC123 JKU354
D ADF789 POP898
JKU354 WER795
D JRE989
POP898
WER795
D YIO809

Fri 24 Oct 2014, 15:46 Trainer Doug said...

RE: DUMP DATA INTO NEW COLUMN FROM SEARCH OF OTHER COLUMN

Hi Adrian

Interesting question! Here's one possible way but only works if there are no gaps in the data in the B column. I've called the procedure ListBlank

Sub ListBlank()
' Counter keeps track of the blank cells in col A
Dim Counter As Integer Range("B1").Select

Do Until ActiveCell = ""
If ActiveCell.Offset(0, -1) = "" Then
Counter = Counter + 1
Cells(Counter, 3) = ActiveCell 'update C column if blank
End If
ActiveCell.Offset(1, 0).Select ' move down one cell
Loop

Hope that does the trick.

Doug
Best STL

End Sub