again display "missing" when blank
 

hello,
for example, i have some data in the range b6:m19, some cells in this range
are blank, so i want them automatically display the word "missing" if there
is no data in
them, thank you.

again display "missing" when blank
 

Hi,

Try this

Private Sub Worksheet_Activate()
Set myrange = Range("B6:M9")
For Each c In myrange
If IsEmpty(c) Then
c.Value = "Missing"
End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrange = Range("B6:M9")
For Each c In myrange
If IsEmpty(c) Then
c.Value = "Missing"
End If
Next
End Sub


Right click the sheet tab, view code and paste it in

Mike

"nada" wrote:

hello,
for example, i have some data in the range b6:m19, some cells in this range
are blank, so i want them automatically display the word "missing" if there
is no data in
them, thank you.

again display "missing" when blank
 

Hi,

Select your range, then:

'Edit' menu - 'Replace' (or just Ctrl+h)

Leave the 'Find what:' box blank and put "missing" in the 'Replace
with:' box.

Hit 'Replace All'.

Cheers,
Ivan.

On Apr 1, 8:59*pm, Mike H wrote:
Hi,

Try this

Private Sub Worksheet_Activate()
Set myrange = Range("B6:M9")
*For Each c In myrange
* * If IsEmpty(c) Then
* * * * c.Value = "Missing"
* * End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
*Set myrange = Range("B6:M9")
*For Each c In myrange
* * If IsEmpty(c) Then
* * * * c.Value = "Missing"
* * End If
Next
End Sub

Right click the sheet tab, view code and paste it in

Mike



"nada" wrote:
hello,
for example, i have some data in the range b6:m19, some cells in this range
are blank, so i want them automatically display the word "missing" if there
is no data in
them, thank you.- Hide quoted text -

- Show quoted text -