Insert specific text in blank cell with VBA

[Livin404]

Greetings, in Column E(5) of worksheet 72 HR I would like “TBD” entered in blank cells. Column D(4) will determine how many rows I need to go down to. In other words any data in Column D then TBD will be placed in Column E. Thank you.

 

[Fluff]

How about

Sub Livin()
   With Sheets("72 HR")
     .Range("E5:E" & .Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Value = "TBD"
   End With
End Sub

 

[veyselemre]

xlBlanks =>
xlCellTypeBlanks

 

[Fluff]

The code works quite happily without that change.

PS. If you want to format code yourself, use the Rich tags rather than the VBA tags

 

[Livin404]

How about

Sub Livin()
   With Sheets("72 HR")
     .Range("E5:E" & .Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Value = "TBD"
   End With
End Sub

Thank you for the prompt reply. I'm afraid I may have not been to clear. It is only with data in Column D that I would need TBA in Column E on the same row. If it is blank in Column D then leave blank in Column E. Cheers!

 

[Fluff]

Are the values in col D hard values or formulae?

 

[Livin404]

There is no formula to get those. They exist before TBA formula is used. So I take it they are hard

 

[Fluff]

Ok, how about

Sub Livin()

   With Sheets("72 HR")
      .Range("D5", .Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Offset(, 1).SpecialCells(xlBlanks).Value = "TBD"
   End With
End Sub

 

[Livin404]

I'm afraid it is still adding a TBD in Column E to a blank cell when there is a blank cell in Column D. Only if there is a time in Column D would I want TBD put in the blank into the corresponding cell in Column E. If a particular cell in Column D is blank then no action is needed. Thank you.

 

[Fluff]

In that case your "blank" cells are not completely empty.