VBA: In a Column Fill empty Cells with Entry from Pop Up Window

[LNG2013]

Hello,
I need assistance with a little bit of VBA code.
The code needs to:

• Identify all the blank cells in Column D, It is the last column.
• Display a pop up dialog / Userform
• A response is entered by the user in the dialog.
• Whatever is entered into that dialog will then fill ALL of the blank cells with the response.

Example of Data Set:

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Food
[/TD]
[TD]Price
[/TD]
[TD]Discount
[/TD]
[/TR]
[TR]
[TD]1/15/2019
[/TD]
[TD]Apple
[/TD]
[TD]1.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/15/2019
[/TD]
[TD]Orange
[/TD]
[TD]1.00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]1/16/2019
[/TD]
[TD]Peach
[/TD]
[TD].75
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/16/2019
[/TD]
[TD]Banana
[/TD]
[TD]2.00
[/TD]
[TD].10
[/TD]
[/TR]
[TR]
[TD]1/16/2019
[/TD]
[TD]Cereal
[/TD]
[TD]3.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/16/2019
[/TD]
[TD]Pop Tarts
[/TD]
[TD]2.50
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]
Dialog : Enter the Discount
User Enters: .25
Result:


[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Food
[/TD]
[TD]Price
[/TD]
[TD]Discount
[/TD]
[/TR]
[TR]
[TD]1/15/2019
[/TD]
[TD]Apple
[/TD]
[TD]1.00
[/TD]
[TD].25
[/TD]
[/TR]
[TR]
[TD]1/15/2019
[/TD]
[TD]Orange
[/TD]
[TD]1.00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]1/16/2019
[/TD]
[TD]Peach
[/TD]
[TD].75
[/TD]
[TD].25
[/TD]
[/TR]
[TR]
[TD]1/16/2019
[/TD]
[TD]Banana
[/TD]
[TD]2.00
[/TD]
[TD].10
[/TD]
[/TR]
[TR]
[TD]1/16/2019
[/TD]
[TD]Cereal
[/TD]
[TD]3.00
[/TD]
[TD].25
[/TD]
[/TR]
[TR]
[TD]1/16/2019
[/TD]
[TD]Pop Tarts
[/TD]
[TD]2.50
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

Try

On Error Resume Next
Range("D:D").SpecialCells(xlCellTypeBlanks).Value = Application.InputBox("fill with what", Type:=2)
On Error Goto 0

 

[LNG2013]

Thank you that works Mike!
Small Snafu - In the example above their are 6 rows, ideally I would want it to stop after the last Row in Column A, any thoughts?
Currently it keeps repeating in Column D into infinity past the last value in the Row for Column A.

 

[LNG2013]

Try

On Error Resume Next
Range("D:D").SpecialCells(xlCellTypeBlanks).Value = Application.InputBox("fill with what", Type:=2)
On Error Goto 0

Thank you that works Mike!
Small Snafu - In the example above their are 6 rows, ideally I would want it to stop after the last Row in Column A, any thoughts?
Currently it keeps repeating in Column D into infinity past the last value in the Row for Column A.