Find blank Cell and paste from certain cell

[rizmomin]

Hello:

I have excel sheet with data in cell N3:O8
Some of the cells may be blank.
I need a VB Code to check if any of the cells are blank and if any cell is blank then copy the content of cell N9 and paste in the blank cell.

Please let me know if any questions.

Thank you

Riz Momin

[ARGK]

Hi
Paste this code into a Module in your workbook:
Sub FillEmptyBlankCellWithValue()
'Fill an empty or blank cell in selection
'with value specified in Cell N9
Dim cell As Range
Dim InputValue As String
Dim Val
Val = Cells.Range("N9")
On Error Resume Next
For Each cell In Selection
If IsEmpty(cell) Then
cell.Value = Val
End If
Next
End Sub
Then select the range N3 to O8 and then run the macro.
Tony

[AB33]

Riz,
Try

Please Login or Register  to view this content.

[nilem]

option

Please Login or Register  to view this content.

[rizmomin]

Hi AB33:

Thank u, you code will work great for me.

Riz Momin

[AB33]

Riz,
You are welcome!

[rizmomin]

Hi AB33:

I need 1 more criteria added when pasting the content of N9 to blank cell.
N9 is non-colored cell and i want to make sure that when replacing blank cell with contents of N9 then the cell of the blank cell should be non-colored.

Please let me know if any questions.

Thank you

Riz Momin

[Winon]

Hello rizmomin,

The code which AB33 gave you does not transfer Cell Interior Color, so no need to worry about that, unless you have conditional Formatting applied to that Range.

[AB33]

Riz,
I think Winon has replied to your question. The code pastes values, (not formatting) only

[rizmomin]

Hi AB33:

How would i paste values with formatting?
I want the interior color and the value of N9 to be pasted.
Please help and let me know if any questions.

Thank you

Riz Momin

[AB33]

Please Login or Register  to view this content.

[Winon]

Hello Riz,

With AB33's permission, change the Code as Follow;

Please Login or Register  to view this content.

[Winon]

@ AB33,

Sorry, you were faster than me.

[AB33]

Winon,
You have killed two birds with stone. I forget the method XLpasteall.

[rizmomin]

Hi AB33 and Winon:

Please refer to attached sheet.
As you can see that the value of N9 is blank and non-color.
I want the same to go to any blank cells between N3:O8.
Its not working like i want with the code.

Please have a look and let me know if any questions.

Thank you

Riz Momin

[Winon]

@ AB33,

Thank you for the feedback, and that you don't mind me butting in. We are all here to learn and help where we can.

It is also a good idea to remember to add;

Please Login or Register  to view this content.

Glad to have you as a member of this great Forum!

[AB33]

Riz,
On the attached Range N9 is blank. Is this right?

[AB33]

Two codes: Mine and Winon's

Please Login or Register  to view this content.

[rizmomin]

Hi AB33 and Winon:

Thanks a lot to both of you for great help.
It works.

Thank you

Riz Momin

[Winon]

Hello Riz,

You are welcome, glad we could help.

Thank you for the "Rep".

@ AB33,

Nice working with you.

[AB33]

Winon and Riz,
You are always welcome!