Search for blank cell and insert concatenated value

[granty]

Hi,

I would like to search a column for blank cell. if a blank cell is found, to concatenate "info required" and text in the previous column

ColA ColB ColC
Joy yes hey
Peace oh
Hope hmm ok

code to search col C, find the blank cell and insert 'info required Peace'

Thank you

 

[Smitty]

It sounds like you want code, but a formula should suffice:

=IF(C1="","Info Required "&A1,"")

HTH,

 

[granty]

Thank you Smitty.

I do want a code. This is because I will download a spreadsheet with a lot of data on it. This table is pivoted on another worksheet
I want to be able to insert a code that automatically searches the column for blank cells and insert the texts above. The reason I want to concatenate the text is so that the blank lines are not grouped together on the pivot table

thanks

 

[Smitty]

You can record a macro putting the formula in the first cell (D2 if you have a header row), then autofill it. Post back what you get and someone can get you set up with a dynamic last row. Or if you set up your data as an Excel Table, then the range will be dynamic automatically.

 

[granty]

Hi. This is what I have from searching 2 cells. I want to do this for all the cells in the column to the last line. Thank you

Sub concatenate()

Columns("D:D").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("D5").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""info reg"",RC[4])"
Columns("D:D").Select
Range("D6").Activate
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range("D11").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""info reg"",RC[4])"
Range("D12").Select
End Sub